Countif w/multiple conditions

Microsoft Office excel 2007 home & stude...
July 8, 2010 at 07:58:08
Specs: Windows XP, 512 MB
I know that there have been a number of questions on this board about the countif function, but after going through page after page and trying the recommended formulas, I'm still stuck.

I have two columns of data. The first is a list of codes, which I am able to sort using a formula such as COUNTIF($E$10:$E$100,">=100000")-COUNTIF($E$10:$E$100,">199999"). That works fine. The second column is a list of 0 and 1 ($K$10:$K$100). My problem is in combining the two columns such that it will count the codes between 100000 and 199999 that also have a 1 in the corresponding column. Any ideas?

Any help is greatly appreciated.

See More: Countif w/multiple conditions

Report •

July 8, 2010 at 12:58:59

Try this:

This formula returns true or false for each comparison, on each row.
So on a row with a value >=100000 in column E, the first section returns true
if the value is also <=199999 in column E the second part returns true, and if column K = 1 the third part returns true.
True evaluates to 1 and false evaluates to zero.

Thus if any of the three parts is false the row evaluates to zero (as anything times zero = zero)

If all three parts are true, the result is 1 * 1 *1 which is 1

SUMPRODUCT sums the results from each row in the range, and in this example returns the number of rows matching all three criteria.

If instead of hard-coding values into the formula you use references to other cells, it is easy to create a table showing the number of rows matching sequential ranges with or without 1 in column K.


Report •

July 8, 2010 at 13:11:29
Terrific! It works like a charm. Thank you so much.

Report •
Related Solutions

Ask Question