Count Using Multiple Criterias

**Vancouver** · 11-06-2010, 01:51 AM

Hi All,

I'm trying to create a formula that will count how many cells in column B fall within/between the range of column C (min) and column D (max).

Please see attachment. Thanks in advance for any input!

Thanks,

Mario

**DonkeyOte** · 11-06-2010, 02:30 AM

Simplest to use 2 COUNTIFs

Please Login or Register  to view this content.

you could use a single SUMPRODUCT but above is preferable IMO.

**protonLeah** · 11-06-2010, 02:42 AM

Please Login or Register  to view this content.

**Vancouver** · 11-07-2010, 12:24 AM

Hi All,

Thank you so much for the inputs!

DonkeyOte, your formula is just what I was looking for however can you explain how the formula below translates?

For example, I would think that $B$B:$B$B,">"&$I2 should be $B$B:$B$B,"<="&$I2 because I need the count of all numbers in $B$B:$B$B less than or equal to $I2.

=COUNTIF($B$B:$B$B,">="&$H2)-COUNTIF($B$B:$B$B,">"&$I2)

Thanks again,

Mario

**DonkeyOte** · 11-07-2010, 04:35 AM

Originally Posted by Vancouver

can you explain how the formula below translates?

For example, I would think that $B$B:$B$B,">"&$I2 should be $B$B:$B$B,"<="&$I2 because I need the count of all numbers in $B$B:$B$B less than or equal to $I2.

The function reads as:

a) Count all transactions greater than or greater than or equal to start date

This figure will be inclusive of transactions that exceed end date obviously so

b) Subtract from that value the Count of all transactions that exceed the end date

The resulting value is thus the count of all transactions between the two dates (inclusive)