Count rows in an excel table where columns satisfy multiple criteria (simulating multi-filter criteria)

Question

I am wondering if there is a simple way to count the number of rows after filtering a subset of columns by value considering multiple selections for a given column in an excel table object.

Let's say I have the following excel table:

A   B   C
a1  b1  c1
a2  b2  c1
a1  b2  c2
a2  b1  c2
a1  b3  c3
a3  b1  c3

saved in an excel table under the name: Table1 and I would like to find all rows that the column A has the value a1 or a2 and column B has the value: b1. The result should be 2.

I am able to do it using SUMPRODUCT function and converting the logical value into [0,1] using the -- operator:

= SUMPRODUCT(--(Table1[A]="a1"),--(Table1[B]="b1")) 
   + SUMPRODUCT(--(Table1[A]="a2"),--(Table1[B]="b1"))

In my real example, I have more than three columns and at least one of them can satisfy multiple criteria so I am wondering if there is a way to do it with a less verbose syntax. For example, I was trying something like this, but it does not work:

= SUMPRODUCT(--(Table1[A]="a1|ab2"),--(Table1[B]="b1"))

or

=SUMPRODUCT(--(Table1[A]=OR("a1", "a2")), --(Table1[B]="b1"))

the OR function does not help, because it does not return an array result, and I cannot use the array formula in my real example because I would need to apply just for one column with more than one selection, but for the rest of the columns I am selecting it is just a single value.

It seems to be a good trick for representing in an excel formula a multiple filter criteria action, but the excel formula is very verbose when within a column it has to satisfy more than one condition, like in the above example.

Under my solution, it would something like this for counting rows in a Table where for each column we filter by only one value except for the first column A that we filter by two possible values:

= SUMPRODUCT(--(Table1[A]="a1"),--(Table1[B]="b1"), 
    --(Table1[C]="c1"), ...Table1[Z]="z1"))
  + SUMPRODUCT(--(Table1[A]="a2"),--(Table1[B]="b1"), 
    --(Table1[C]="c1"), ...Table1[Z]="z1"))

cybernetic.nomad cybernetic.nomad · Accepted Answer · 2019-03-28T19:24:33

Try:

=SUMPRODUCT((Table1[A]="a1")+(Table1[A]="a2"),--(Table1[B]="b1"))

Since any given cell's cannot both be "a1" and "a2", the sum will be 1 if either is true and 0 if neither is true

Count rows in an excel table where columns satisfy multiple criteria (simulating multi-filter criteria)

1 Answers