CODEWITHSUNDEEP
excelexcel-formula
madstap
madstap

Reputation: 1552

Count by row on based on more than one column

I have data shaped like the following in excel:

A       B  C  D
"foo"   5  3  1
"foo"   2  4  5
"foo"   5  5  5
"bar"   1  2  3
"bar"   4  5  7

I want to know how many rows contain "foo" in column A and 5 in either one of column B, C or D.

In other words, I want the following formula =COUNTIFS(A1:A5;"foo";B1:B5;5;C1:C5;5;D1:D5;5), but with the B, C and D ranges or'ed together instead of and'ed. Is there a simple way to do this with an excel formula?

Upvotes: 3

Views: 95

Answers (2)

aspan88
aspan88

Reputation: 617

If we can use a extra column then also we can achieve this task

A   B   C   D   E
foo 5   0   1   =COUNTIF(B2:D2,5)(1)
foo 2   5   5   =COUNTIF(B3:D3,5)(2)
foo 5   5   5   =COUNTIF(B4:D4,5)(3)
bar 1   5   3   =COUNTIF(B5:D5,5)(1)
foo 4   0   7   =COUNTIF(B6:D6,5)(0)

Then, we can use countifs

=COUNTIFS(A:A,"foo",E:E,">0") = 3

Upvotes: 0

user4039065
user4039065

Reputation:

Try,

=SUMPRODUCT((A1:A5="foo")*SIGN((B1:B5=5)+(C1:C5=5)+(D1:D5=5)))

Upvotes: 9

Related Questions