count rows in excel with multiple matched criteria

0
votes

I have the following data in a excel sheet and would like to count the number of rows that match specific criteria.

   A      B        C
1  Name   Status   Version
2  Joe    Open     1.0
3  Bob    Open     1.0
4  Joe    Closed   1.0
5         Open     1.0
6  Joe    Open     2.0

I would like to count all rows where;

  1. Name is not empty, AND
  2. Status is not "Closed", AND
  3. Version is not "2.0"

based on the sample data above the count would be 2 (row 2 and 3 on the sheet)

How could I achieve this?

Thanks in advance.

5
excel

5 Answers

2
votes

Use this Array Formula:

=SUM(IF(A2:A6<>"";1)*IF(B2:B6<>"Closed";1)*IF(C2:C6<>"2.0";1))

And hit CTRL+SHIFT and ENTER

The formula will evaluate to 2 rows as you suggested.

2
votes

You can use COUNTIFS() for this:

=COUNTIFS(A1:A5, "<>",B1:B5, "<>Closed",C1:C5, "<>2")

That first comparison with A1:A5 being "<>" is a little tricky, but it's just saying "If A1:A5 IS NOT NULL"

1
votes

Another possibility: advanced use of the SUMPRODUCT() function:

=SUMPRODUCT(--(A1:A5<>""),--(B1:B5<>"Closed"),--(C1:C5<>"2.0"))
1
votes

If you're using Excel 2013, COUNTIFS() is what you're looking for.

=COUNTIFS(A2:A6,"<>"&"",B2:B6,"<>"&"closed",C2:C6,"<>2")

Here's Microsoft's documentation on COUNTIFS().

Here's a link for how to use criteria in COUNTIF() and COUNTIFS().

0
votes

Use a pivot table and set the filters with what You want. Put anything in the value as count. Many ways to handle this in a pivot.