CODEWITHSUNDEEP
excelexcel-formula
iambasil
iambasil

Reputation: 105

Can a countifs do this or do I need an array?

I have a data range B5:L100.
In column B is a string identifier, say 'X' or 'Y'.
In columns C:L we have different people's names entered (never more than once per row).

I want to count how many times a person's name appears in rows where column B is 'X'. The following formula doesn't work (using "Max" as the example person to search for).
Can you advise on what will do this elegantly?

=COUNTIFS(C5:L100,"Max",B5:B100,"X")

I think an array formula might be in order, but I'm not too experienced on those.

Upvotes: 1

Views: 76

Answers (2)

XOR LX
XOR LX

Reputation: 7762

=SUMPRODUCT((C5:L100="Max")*(B5:B100="X"))

Upvotes: 1

iambasil
iambasil

Reputation: 105

One way to do this is to just do it column by column:

=COUNTIFS(C5:C100,"Max",B5:B100,"X")+COUNTIFS(D5:D100,"Max",B5:B100,"X")... etc

Does the trick, but not too elegant if you have loads of columns to look through. I'm sure there's a tidier way using an array formula.

Upvotes: 2

Related Questions