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

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

• :biggrin:

Thank you all. You have been a great help.

• 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!