Formulas: COUNTIF

  • Hi,

    I am trying to create a formula and having great difficulty. I have a spreasheet with employee absence recorded on it.

    I need to create a formula that will look down one column to find a particular employee code and then look down another column to find an absence type and sum a different another column where these two conditions are matched.

    I initially thought a SUMIF function would work but cannot use multiple conditions.

    Can anybody help me please?



    =SUMPRODUCT(($A$1:$A$100 = CONDIT1)*($B$1:$B$100 = CONDIT2),($C$1:$C$100))

    Where CONDIT1 & CONDIT2 are your 2 conditions to match...

    Hope this helps

  • =SUMPRODUCT(('Absense - Sickness'!B:B=B2)*('Absense - Sickness'!I:I="S"),'Absense - Sickness'!K:K)

    Can anybody spot the problem with my formula. I recieve a NUM error.

  • I believe Excel is having a problem remembering all of the values in your three columns. Try limiting the ranges. For example
    =SUMPRODUCT(('Absense - Sickness'!B1:B5000=B2)*('Absense - Sickness'!I1:I5000="S"),'Absense - Sickness'!K1:K5000)

  • Better late than never! :biggrin:

    SUMPRODUCT will work with rows upto and including 65,535 but will bug out on a whole column - don't ask me why, it just does ok :)


    =SUMPRODUCT(('Absense - Sickness'!B1:B65535="Apple")*('Absense - Sickness'!I1:I65535="S"),'Absense - Sickness'!K1:K65535)

    will work fine, but

    =SUMPRODUCT(('Absense - Sickness'!B:B="Apple")*('Absense - Sickness'!I:I="S"),'Absense - Sickness'!K:K)

    will not....


Participate now!

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