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:
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:
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?