CODEWITHSUNDEEP
excel
User247365
User247365

Reputation: 685

COUNTIFS with multiple Ranges, Single Criteria

I have the data set below.

     A           B           C           D
 1   Apple       Pear        Grape       Orange
 2   Blueberry   Grape
 3   Pear        Orange
 4   Apple       Grape       Orange
 5   Grape       Orange      Blueberry
 6   Pear
 7   Grape       Apple

I want to count the rows in which either Apple OR Pear appear. So the answer to the above should end up being 5 (rows 1,3,4,6 and 7). Row 1 where both apple and pear appear should only be counted once. In my actual data set, I have 4 columns and upwards of 250 rows.

Among other attempted solutions, I have tried 

 =COUNTIFS(A1:A7,"Apple")+COUNTIFS(A1:A7,"Pear")+COUNTIFS(B1:B7,"Apple")+COUNTIFS(B1:B7,"Pear")+COUNTIFS(C1:C7,"Apple")+COUNTIFS(C1:C7,"Pear")+COUNTIFS(D1:D7,"Apple")+COUNTIFS(D1:D7,"Pear")

but that double counts row 1 giving one extra count than I am looking for.

If it is simpler, I know how many rows I have, so a solution that involves determining how many rows do not contain either apple or pear would be sufficient.

Upvotes: 0

Views: 241

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use:

=SUMPRODUCT(--(COUNTIF(OFFSET($A$1,ROW(A1:A7)-1,0,1,4),"Apple")+COUNTIF(OFFSET($A$1,ROW(A1:A7)-1,0,1,4),"Pear")>0))

If goes row by row and does two COUNTIFS on each Row and adds them together. If that row is greater than 0 it adds 1 to the SUMPRODUCT.

enter image description here

Or this that uses MMULT to return the correct Number:

=SUM(N(MMULT(N((A1:D7="Apple")+(A1:D7="Pear")),TRANSPOSE(COLUMN(A1:D7)^0))>0))

This is an array formula and must be confirmed with Ctrl-Shift-Enter instead of Enter when Exiting edit mode.

The advantage with this over the first is this one is not Volatile.

enter image description here

Upvotes: 3

Related Questions