0

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? – Akina Aug 2 '18 at 4:38
0

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")
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.