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.

---

ஜெ.இரவிச்சந்திரன்

Answer 1

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

Answer 2

Bernie,

Does this really need the helper column? I think the sumproduct function answered the question by itself.  Just wondering...

Eric

Answer 3

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