SUMIF/COUNTIF problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to sum all occurences of a value in Column A, if another value
occurs in column B in the same row... but I cant figure out how to do it.
Essentially I want to calculate 'How many times does Column A = 1 when Column
B = Yes'? Any help would be most appreciated.
 
Use SUMPRODUCT if you have more than one condition to check.

=SUMPRODUCT(--(A1:A100=1),--(B1:B100="Yes"))

HTH,
Elkar
 
Many thanks for the quick reply. I have tried this, but I'm still getting
errors. The two columns are in another worksheet - would this make a
difference?

I assume that the '--' in the formula are dashes
 
Yes, you have to tell Excel where the data is, as in
=SUMPRODUCT(--(SheetName!A1:A100=1),--(SheetName!B1:B100="Yes")). If you
don't specify on which sheet the data is, Excel assumes the data is on the
sheet where the formula is.
 
Tyro,

Many thanks - I've found my error - I was using A:A instead of A1:A100 (I
wanted to check all rows in the columns)

I've simply set the row to 200 to ensure that it counts all rows

Thanks again

Tone
 
Back
Top