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.

Hi, Try this:

=SUMPRODUCT(($E$10:$E$100>=100000)*($E$10:$E$100<=199999)*($K$10:$K$100=1))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.

Regards

Terrific! It works like a charm. Thank you so much.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History