SUMPRODUCT is making me mad!

[freseh]

Hello all excel experts

My problem:
I have a list with two columns A and B and I want to sum column B base
on filtred multiple criterias in column A. An example on the list:
Column A Column B
test200302abc 300
test200311dsf 400
test200312dsj 500
test200312adf 400
test200401xyz 100
test200402fsf 100
test200403sdf 300
test200403fsf 200
test200401fsf 100

Now I want to sum all values that meet the criteria *200312* o
*200401* or *200403*, which sums up in this example to 1600.
My easy solution is to have a lot of sumif formulas but when I have u
to 24 criterias it's too much I think.
So I hoped that my other solution would be like this:
SUMPRODUCT((A:A="*"&"200312"&"*")*(A:A="*"&"200401"&"*")*(A:A="*"&"200403"&"*")*(B:B)
but it returns just #NUM!

What´s wrong? Any suggestions on a alternative solution?

All the best to THE ONE that solve my problem

 

[Frank Kabel]

Hi
several things are wrong
- SUMPRODUCT does not accept ranges like A:A. You have to use something
like A1:A1000
- SUMPRODUCT does not support wildcards (like SUMIF)

One solution:
=SUMPRODUCT(--(ISNUMBER(FIND("200312",A1:A1000))+ISNUMBER(FIND("200401"
,A1:A1000))+ISNUMBER(FIND("200403",A1:A1000))>0),B1:B1000)

 

[Bernard Liengme]

Couple of problems here.
(1) SUMPRODUCT cannot cope with A:A; you need A1:A63000 (or similar)
(2) The 'like' operator does not work this way. Try using MID
(3) You are asking that the A cell contains both 200313 AND 200401 when you
mean to test if it contains 20031 OR 200401
Try
SUMPRODUCT((MID(A1:A10,5,6)="200312")+(MID(A1:A10,5,6)="200401")+(MID(A1:A10
,5,6)="200403"),B1:B10)
Replace A10 and B10 to include the range to be tested

Best wishes

 

[Bernard Liengme]

Hi Frank,
The double negation is not needed here since the addition operator coerces
Boolean to numeric
Best wishes
Bernard

 

[Frank Kabel]

Hi Bernard
not so sure as I first compare the added ISNUMBER result with '>0'.
This would create a single boolean value (though one could argue if
this comparison is required.).

 

[Harlan Grove]

...
...

Try
SUMPRODUCT((MID(A1:A10,5,6)="200312")+(MID(A1:A10,5,6)="200401")+(MID(A1:A10
,5,6)="200403"),B1:B10)
Replace A10 and B10 to include the range to be tested

...

Why call MID 3 times? These are mutually exclusive possibilities, so

=SUMPRODUCT((MID(A1:A10,5,6)={"200312","200401","200403"})*B1:B10)

 

[Harlan Grove]

...
...

One solution:
=SUMPRODUCT(--(ISNUMBER(FIND("200312",A1:A1000))+ISNUMBER(FIND("200401"
,A1:A1000))+ISNUMBER(FIND("200403",A1:A1000))>0),B1:B1000)

Or, since these are mutually excludive possibilities,

=SUMPRODUCT(ISNUMBER(FIND({"200312","200401","200403"},A1:A1000))*B1:B1000)