SUMPRODUCT is making me mad!

  • Thread starter Thread starter freseh
  • Start date Start date
F

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
 
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)
 
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
 
Hi Frank,
The double negation is not needed here since the addition operator coerces
Boolean to numeric
Best wishes
Bernard
 
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.).
 
...
...
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
Click to expand...
...

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

=SUMPRODUCT((MID(A1:A10,5,6)={"200312","200401","200403"})*B1:B10)
 
...
...
One solution:
=SUMPRODUCT(--(ISNUMBER(FIND("200312",A1:A1000))+ISNUMBER(FIND("200401"
,A1:A1000))+ISNUMBER(FIND("200403",A1:A1000))>0),B1:B1000)
Click to expand...

Or, since these are mutually excludive possibilities,

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