Countfs with ELSE I think

    Hi All.


    I'm trying to get a COUNTIFS to work where it looks at a date and an ID number then counts data in 3 columns. However, what it needs to do is look at the first column, if it is 0.00 then ignore it, if the next column is 0.00, ignore that too but if there is a number greater that 0.00 in the last column, count it as 1


    If there are figures are greater than 0.00 in one or more of the columns then also count it as 1 and total up all the 1's in the formula column.


    I've attached a sample, I really hope that helps.


    Any assistance will be gratefully received, thank you......


    Kind regards


    DezB


    Countifs.xlsx

    Why would row 5 count as 1 when none of the numbers are greater than 0 (the first one being -25)?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Hi Rory.


    That's where I also have a quandary as it needs to count is a 1. Basically what's being recorded is if there is a change in the position of funding. I was trying to start with greater than 0 but I guess what it really needs to look at is, is there a figure anything other than 0.00. I hope this helps, it's been a long week, although it's been a short week in the UK :)

    Kind regards


    DezB

    It would be much simpler if you can add a helper column in say column F using:

    =COUNTIF(C5:E5,"<>0")

    then your formula in O5 is simply:

    =COUNTIFS($G$5:$G$13,$O$4,$B$5:$B$13,$L5,$F$5:$F$13,">0")

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Like 2

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login