CODEWITHSUNDEEP
excel
Jamie
Jamie

Reputation: 169

count rows in excel with multiple matched criteria

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.

Upvotes: 0

Views: 228

Answers (5)

Carl Kevinson
Carl Kevinson

Reputation: 795

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().

Upvotes: 1

m0r0dan
m0r0dan

Reputation: 149

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

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

Upvotes: 1

guesto
guesto

Reputation: 1

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.

Upvotes: 0

JNevill
JNevill

Reputation: 50263

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"

Upvotes: 2

AnalystCave.com
AnalystCave.com

Reputation: 4984

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.

Upvotes: 2

Related Questions