CODEWITHSUNDEEP
excelexcel-formulams-office
Arjan_IO
Arjan_IO

Reputation: 175

Count number of X in Column A when Y is in either Column B or C

Simplified data in columns A, B and C:

A | B | C
X |   | Y
  | Y |
X | Y |
Z |   | Y
X |   |

How to count the number of X when the value of Y is present in column B or C? (ie. result is 2). Was thinking about COUNTIFS but can't seem to get the formula right :(

Upvotes: 0

Views: 1000

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Because you never have a double-Y case, you can use:

=SUMPRODUCT((A1:A5="X")*(B1:B5="Y"))+SUMPRODUCT((A1:A5="X")*(C1:C5="Y"))

enter image description here

Upvotes: 1

Chronocidal
Chronocidal

Reputation: 7951

You can't do this with a single COUNTIFS, because conditions in COUNTIFS are AND, not OR. You can do it with multiple COUNTIFS, or an Array Formula (not recommended - that's a bit overkill for this case)

You need to count up all the rows where Column A is X and Column B is Y, add all the rows where Column A is X and Column C is Y, and then decide of a row where Column A is X and both Column B and Column C are Y is possible and/or should be counted twice.

If it is possible, but should not be double-counted, then you will need to subtract all the rows where Column A is X and Columns B and C are both Y.

=COUNTIFS($A:$A,"X",$B:$B,"Y") + COUNTIFS($A:$A,"X",$C:$C,"Y") - COUNTIFS($A:$A,"X",$B:$B,"Y"",$C:$C,"Y")

If it can't be in both, or you want that to count as 2 rows instead of 1, then you don't need the third COUNTIFS

As an Array Formula (using SUMPRODUCT instead of SUM, because then we don't need to use Ctrl+Shift+Enter), for reference:

=SUMPRODUCT(--($A:$A="X")*--(--($B:$B="Y")+($C:$C="Y")>0))

(The >0 is so that the OR (+) doesn't double-count)

Upvotes: 3

Related Questions