# SUMPRODUCT Formula help

This is a discussion on SUMPRODUCT Formula help within the Excel Questions forums, part of the Question Forums category; Hello All, I want to count the number of cells in Column B that = "A", "B", "C" IF Column ...

1. ## SUMPRODUCT Formula help

Hello All,

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

2. ## Re: SUMPRODUCT Formula help

Welcome to the Board.

In what way doesn't your first formula work?

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

3. ## Re: SUMPRODUCT Formula help

Hi,

Welcome to board

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

Try removing --

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

4. ## Re: SUMPRODUCT Formula help

Hi Andrew,

When I have only one criterion in my second array, it returns the result I expect.
=SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="A"))

As soon as I add another criterion:
=SUMPRODUCT(--(ColB={"A","B","C"})*(ColA={"A","B"}))

I get the error #N/A 'a value is not available for the formula or function'

Thanks,
Ben

5. ## Re: SUMPRODUCT Formula help

Hi Segran,

The behaviour is the same with or without the --.

Thank you.

6. ## Re: SUMPRODUCT Formula help

If you have an unequal numnber of conditions:

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

7. ## Re: SUMPRODUCT Formula help

Ah ha!

Thank you very much indeed - that works a treat.

Ben

8. ## Re: SUMPRODUCT Formula help

Try this

 a a c d a a c d c a c e d b a e e b a e f b a a g c c a

=IF(AND(COUNTIF(A24:A30,B24)>0,COUNTIF(C24:C30,B24)>0,COUNTIF(D24:D30,B24)>0),COUNTIF(B24:B30,B24))

Thanx,
Yuvaraj

9. ## Re: SUMPRODUCT Formula help

Thanks for the reply - I will experiment with that solution too.

Ben