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


"tonefbr" wrote:

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.

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

"Elkar" wrote:

Use SUMPRODUCT if you have more than one condition to check.

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

HTH,
Elkar


"tonefbr" wrote:

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.

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.

"tonefbr" wrote in message
...
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

"Elkar" wrote:

Use SUMPRODUCT if you have more than one condition to check.

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

HTH,
Elkar


"tonefbr" wrote:

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.

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

"Tyro" wrote:

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.

"tonefbr" wrote in message
...
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

"Elkar" wrote:

Use SUMPRODUCT if you have more than one condition to check.

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

HTH,
Elkar


"tonefbr" wrote:

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.