CODEWITHSUNDEEP
excelexcel-formula
David Leal
David Leal

Reputation: 6749

Counting Rows in Excel based on optional filter conditions

I would like to count the number of rows in excel based on multiple optional conditions. I am using COUNTIFSF function and it works, but as the conditions are growing up, the excel formula needs to consider all possible combinations, making the formula very large. I am wondering if there is a better way to do it.

Here is a sample for two filters (one per column), in both cases, I would like to consider each one of them could be optional. It is represented by the value: ALL on each cell:

Sample using two optional filters

I am using the following formula for cell C10:

=IF(
  AND(B1<>"ALL", B2<>"ALL"), COUNTIFS(TB[Col1],B1, TB[Col2],B2), 
    IF(AND(B1<>"ALL", B2="ALL"), COUNTIFS(TB[Col1],B1,TB[Col1],"*"), 
      IF(AND(B1="ALL", B2<>"ALL"), COUNTIFS(TB[Col1],"*", TB[Col1],B2), 
      COUNTIFS(TB[Col1],"*", TB[Col1],"*"))))

Where TB is the excel table object I created

In case I assign the value ALL to both filters it counts all the rows for a given column, for example, the scenario of Filter1=ALL and Filter2=ALL will return 6 and for: Filter1=A and Filter2=ALL will return the value: 2

As you can see, adding another filter and column will require 8 possible combinations to evaluate. In my real example, I would need 4 filters, so this solution doesn't escalate.

Another workaround would be adding additional unnecessary columns indicating which cells to consider (0, 1) based on the filter's values. It would imply having an additional column per filter condition. I am wondering if there is a simpler way to do it, without using a pivot table.

Note: Adding a text representation to facilitate to reproduce it in excel:

Filter1 ALL     Col1    Col2
Filter2 ALL     A       AA
                B       BB
                C       CC
                D       DD
                A       AA
                D       DD

Upvotes: 1

Views: 458

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Put the IFs inside the COUNTIFS:

=COUNTIFS(TB[Col1],IF(B1="ALL","*",B1),TB[Col2],IF(B2="ALL","*",B2))

enter image description here

Upvotes: 2

Related Questions