count with multiple conditions

  • Thread starter Thread starter John Hatch
  • Start date Start date
J

John Hatch

I am trying to find a formula to count the number of rows that have BOTH a
"4" in column C AND a "ph" in column H

My info shows ={SUM(IF(Apr!$C$2:$C$1500="4",IF(Apr!$H$2:$H$1500="ph",1,0)))}
should work but it only returns a '0'

Any and all help would be appreciated

Thanks,
John
 
=SUMPRODUCT((Apr!$C$2:$C$1500="4")*(Apr!$H$2:$H$1500="ph"))
You may ignore the ws references if you are on the same ws.
 
Just for the record, JE has given you an answer to solve your problem, but for
info only, the formula you provided will not work anyway if it is as you state.
The curly brackets denote an array formula, but you do NOT put them in yourself.
Excel enters them for you when you array enter a formula using CTRL+SHIFT+ENTER,
and when it does the first curly bracket will be BEFORE the = sign, eg:-

{=SUM(IF(Apr!$C$2:$C$1500="4",IF(Apr!$H$2:$H$1500="ph",1,0)))}
 
Back
Top