Multiple column count

[Gamecraze]

Hi, I cannot find a answer to this anywhere.

I need to do this
Count how many times Both A and ABC in cells A1 and B1 are on the same row within Column C and D via formula
in this case the answer is 3 times


A|B|C|D
A|ABC|A|ABC
.|.|B|ABC
.|.|C|ABC
.|.|A|BAC
.|.|A|CBA
.|.|B|CBA
.|.|C|BAC
.|.|A|ABC
.|.|A|ABC

[Paul]

Game, try this formula:

Please Login or Register  to view this content.

Change your ranges to suit. I used C1:C9 and D1:D9 as an example.

[Gamecraze]

Cannot seem to get it to work. Mabey just me i dunno
=SUMPRODUCT((--$B$2&$F$1=K2:K9&M2:M9))

My actual numbers for testing are b2 and f1, and columns K and M
Ideally, it'll be K:K and M:M instead of k1:k1000 or whatever

I've tried:
=COUNT(AND(MATCH(B$2,K:K,FALSE),MATCH(F$1,M:M,FALSE)))
and lots of other incomplete stuff with match, countif, sum (not sumproduct tho)

[BigBas]

The problem is that you copied paul's formula incorrectly.

=sumproduct(--(a1&b1=c1:c9&d1:d9))

Also, note that you cannot use an entire column as your range with the sumproduct formula. You cannot use K:K, although you can use K1:K65000

[Gamecraze]

Thank you very much both of you, I believe I got what i needed working with that.

[Paul]

Glad you have it sorted out now. Thanks for letting us know.