COUNTIF based on 2 criteria

  • Thread starter Thread starter George Wilson
  • Start date Start date
G

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





.
Click to expand...
 
Back
Top