# 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

• 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 Thursday, December 9, 2010 6:48 PM
• Marked as answer by 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 Thursday, December 9, 2010 6:48 PM
• Marked as answer by 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, Bernie
Thursday, December 9, 2010 6:44 PM