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!

2 Answers

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.