# Excel formula to count the number of rows that meets one or more of many criteria

Is it possible to count the number of rows in a worksheet that meet at least one criteria?

For example, given the following table,

``````  A          B        C         D
1 Vehicle    1 wheel  2 wheels  4 wheels
2 Unicycle   Y        N         N
3 Bicycle    N        Y         Y
4 Motorbike  N        Y         N
5 Car        N        N         Y
``````

I wish to get the number of vehicles that have either 2 OR 4 wheels. The answer would be 3 in this case.

SUM(COUNTIFS) seems to return the number of cells with OR logic, and would give me 4 CELLs that meet at least 1 criteria. SUMPRODUCT() seems to return the number of rows with AND logic, and would give me 1 row that meets BOTH criteria. How do I get a formula that returns the number of ROWS that meet AT LEAST ONE criteria?

Thanks!

• SUM(COUNTIFS) seems to return the number of cells with OR logic, and would give me 4 CELLs that meet at least 1 criteria. It's because you include column' B cells into checked range... but why? Aug 2 '18 at 4:38

You are pretty much on the answer with what you already found out. Try:

``````=COUNTIFS(D3:E6,"Y")-COUNTIFS(D3:D6,"Y",E3:E6,"Y")
``````

I like an old-school solution, make a new column that just tests your criteria like:

``````=IF(OR(D3="Y",E3="Y"),1,0)
``````

Then run it down the side and sum it at the bottom

``````row A         B       C        D        E
1   Vehicle   1 wheel 2 wheels 4 wheels Good?
2   Unicycle  Y       N        N        0
3   Bicycle   N       Y        Y        1
4   Motorbike N       Y        N        1
5   Car       N       N        Y        1

sum                                 3
``````

SUMIFS() will definitely work, but this 'Good' column is easy to look at, and has added benefits:

• when it gets more complicated you can audit it
• you can copy the column, then /esv (more old school), and delete the source columns
• you can sort, filter, etc.