Counting based on conditions

[excel_padwan]

So, I've been out of the excel game for a while now and I'm finding I need to get back in. It's true that you lose it if you don't use it. So, I'm finding myself in the following situation.

I have a column, a value of 1-4 in each cell. I need to be able to take that column's values, and based on if a cell in that column contains a 1, find the count of another column that contains a specific value. So, for example.

I thought it would be this formula, but it's not working right.

=IF(COUNTIF(A2:A1106,1),(COUNTIF(BD2:BD1106,1)),0)

That counts all the cells in bd regardless if a has a one in that row or not.

Thank you for any suggestions.
J

 

[VoG]

Try

=SUMPRODUCT(--(A2:A1106=1),--(BD2:BD1106=1))

 

[T. Valko]

So, I've been out of the excel game for a while now and I'm finding I need to get back in. It's true that you lose it if you don't use it. So, I'm finding myself in the following situation.

I have a column, a value of 1-4 in each cell. I need to be able to take that column's values, and based on if a cell in that column contains a 1, find the count of another column that contains a specific value. So, for example.

I thought it would be this formula, but it's not working right.

=IF(COUNTIF(A2:A1106,1),(COUNTIF(BD2:BD1106,1)),0)

That counts all the cells in bd regardless if a has a one in that row or not.

Thank you for any suggestions.
J

Maybe one of these...

For ALL versions of Excel:

=SUMPRODUCT(--(A2:A1106=1),--(BD2:BD1106=1))

For Excel 2007 and later:

=COUNTIFS(A2:A1106,1,BD2:BD1106,1)

The COUNTIFS version is more efficient if you can use it.

 

[excel_padwan]

Solved! Both ways work great. Thank you both for the response, this will certainly help my data sorting go by quicker.

-J

 

[T. Valko]

Solved! Both ways work great. Thank you both for the response, this will certainly help my data sorting go by quicker.

-J

You're welcome. We appreciate the feedback!