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?

    Use SUMPRODUCT


    i.e.


    =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)
    works.

    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 :)


    so


    =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....


    Will

Participate now!

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

Register Yourself Login