[ms office - 2003] SUMPRODUCT Formula help

[park_bench]

Hello All,

 

Fairly advanced Excel formula help please...

 

I want to count the number of cells in Column B that = "A", "B", "C" IF Column A = "A", "B", "C"

 

I can't get a SUMPRODUCT to work with multiple criteria in both columns (or a wildcard in one column).

 

I have tried:

=SUMPRODUCT(--(ColB={"A","B","C"})*(ColA={"A","B","C"}))

=SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="*"))

 

Neither work.

 

I could use an intermediate calculation, but I'm trying to avoid it. I could use:

=SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="A"))+SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="B"))+...

 

but that's messy.

 

Anyone any ideas? Thanks in advance

[park_bench]

Hi all,

 

solution to this problem is on another forum. SUMPRODUCT Formula help - MrExcel Message Board

 

Hope it helps,

Ben