# 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

#1 July 8, 2010 at 12:58:59
 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 trueif 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 1SUMPRODUCT 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

Report •

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

Report •
Related Solutions 