Answered by:
counting number of rows satisfying certain conditions
Question

In excel ,I want to count number of rows satisfying certain conditions .That is if column A contains "x " and column C contains "y", then it should be counted as 1.For this ,I can use filters and count function.But,I want VB to do it.Because,I need the 'counts' on so many number of conditions at one go. So,I believe a VB macro will list all of them immediately.Please help with the macro.
ஜெ.இரவிச்சந்திரன்Thursday, December 9, 2010 5:16 PM
Answers

Set up a table of your condition combinations in a two column table, then in the third column use a formula. Say that your table is in M2:N20  in O2, enter the formula
=SUMPRODUCT(($A$2:$A$1000=M2)*($C$2:$C$1000=N2))
and copy down to match your table.
You could also use a pivot table, which would list all possible combinations without formulas  or you could limit which ones are shown or counted.
HTH, Bernie Proposed as answer by evohnave Thursday, December 9, 2010 6:48 PM
 Marked as answer by Bessie Zhao Friday, December 17, 2010 9:09 AM
Thursday, December 9, 2010 5:51 PM
All replies

Set up a table of your condition combinations in a two column table, then in the third column use a formula. Say that your table is in M2:N20  in O2, enter the formula
=SUMPRODUCT(($A$2:$A$1000=M2)*($C$2:$C$1000=N2))
and copy down to match your table.
You could also use a pivot table, which would list all possible combinations without formulas  or you could limit which ones are shown or counted.
HTH, Bernie Proposed as answer by evohnave Thursday, December 9, 2010 6:48 PM
 Marked as answer by Bessie Zhao Friday, December 17, 2010 9:09 AM
Thursday, December 9, 2010 5:51 PM 
Bernie,
Does this really need the helper column? I think the sumproduct function answered the question by itself. Just wondering...
Eric
Thursday, December 9, 2010 6:01 PM 
It doesn't _need_ the helper table, but it does make for a nicer report (duplicated values are removed) and allows for selection of the desired combinations.
HTH, BernieThursday, December 9, 2010 6:44 PM