COUNTIF based on 2 criteria

[George Wilson]

I am wanting to do a COUNTIF or SUM IF type function based
on coordinates from 2 seperate columns. I want to COUNTIF
(A1:A9=1 and b1:b9=1), but I cannot seem to get the syntax
right. Should I be using a Sum(IF... type function to
acceive this? I tried a =SUM(IF((A1:A9=1)+(B1:B9=1),1,0))
but this does not come up with the correct sum. Can anyone
give me some help with the syntax here?
TIA
George

 

[Frank Kabel]

Hi George
you have to use SUMPRODUCT for this (as SUMIF/COUNTIF only support one
criteria). e.g.
=SUMPRODUCT((A1:A9=1)*(B1:B9=1))
to count
or
=SUMPRODUCT((A1:A9=1)*(B1:B9=1),C1:C9)
to sum all values in column C
Frank

 

[Guest]

this works great...
Thank you

-----Original Message-----
Hi George
you have to use SUMPRODUCT for this (as SUMIF/COUNTIF only support one
criteria). e.g.
=SUMPRODUCT((A1:A9=1)*(B1:B9=1))
to count
or
=SUMPRODUCT((A1:A9=1)*(B1:B9=1),C1:C9)
to sum all values in column C
Frank





.