MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sumproduct, multiple conditions


Posted by Catherine Munro on January 18, 2002 12:36 PM

I'm trying to use this array formula, based on others I've seen Aladin post here:

={SUMPRODUCT(Dollars!$J$4:$J$10000=$R19)*(Dollars!$H$4:$H$10000=$Q19),(Dollars!$K$4:$N$10000))}

Column H stores product category.
Column J stores vendor name.
Columns K through DJ store weekly sales data; some cells are blank.

It doesn't seem to be working (generating VALUE error) -- did I typo? (yes, I control-shift-entered it.) Do the blank cells affect it?

Once this part works, I'll need to modify it so that the Worksheet referred to (Dollars, Qty, or Profit) is stored in another cell (W15) -- I've done this elsewhere with "=INDIRECT(W15 & "!$J$4:$J$10000"). Will that cause problems with SUMPRODUCT?

Once all THAT works, I need to sum a variable date range within columns K through DJ: e.g., user chooses Week 13 to Week 26 -- I need to sum columns W through AJ for that vendor and product category.

I've done it with just the one criteria, Vendor, this way:

=SUM(IF(INDIRECT($S$17&"!$J$4:$J$10000")=$R19,OFFSET(INDIRECT($S$17&"!$J$4:$J$10000"),0,$B$4,9996,$B$5-$B$4+1)))

S17 stores worksheet referred to (Dollars, Qty or Profit)
J4:J10000 stores vendor names for each item.
R19 stores vendor name.
B4 stores beginning week number in user-chosen range.
B5 stores ending week number.

How can I make this work with two conditions?

Thanks!
Catherine


Posted by bob Umlas on January 18, 2002 12:49 PM

1 - You don't need to use ctrl/shift/enter for SUMPRODUCT.
2 - you DO need an asterisk at the end. Instead of
=SUMPRODUCT(Dollars!$J$4:$J$10000=$R19)*(Dollars!$H$4:$H$10000=$Q19),(Dollars!$K$4:$N$10000))
you need
=SUMPRODUCT(Dollars!$J$4:$J$10000=$R19)*(Dollars!$H$4:$H$10000=$Q19)*(Dollars!$K$4:$N$10000))
3 - Blanks should not affect it
4 - INDIRECT should work fine
5 - you make that last bit work with 2 conditions by a similar construction of a SUMPRODUCT formula.

Posted by Juan Pablo G. on January 18, 2002 1:00 PM

There's also a Parenthesis missing at the beginning, should read...

ERR

Posted by Aladin Akyurek on January 18, 2002 1:00 PM

adding to Bob's list, if you have anywhere formula returned blanks ("") in a range of essentially numeric type, either ler those formulas return a zero or add an ISNUMBER test to the SUMPRODUCT formulas.

Aladin

Posted by Russell Hauf on January 18, 2002 4:07 PM


Actually, with point #2, the commma will work. Try it! :)

Posted by Aladin Akyurek on January 18, 2002 4:12 PM

That's right Russell. Read the list too fast. I think the main issue would be the formula returned blanks.