Multiple Criteria for Sumproduct

  • Thread starter Thread starter Pam
  • Start date Start date
P

Pam

Hi,

I have a worksheet that is calculated by salespersons and by item class. I
have a summary on same worksheet that uses sumproduct to calculate only
twelve of the item classes. Works great, but now I need it to show a total
for all those remaining and it is picking up my "total" lines throughout the
worksheet. I tried placing a wildcard <>*total, but then read sumproduct
doesn't support wildcards. Below is the code I am using to return all other
classes. If anyone has a suggestion as to how to not include the total
lines, I would appreciate it.

=SUMPRODUCT(1-($B$2:$B$196="DNU")-($B$2:$B$196="dou")-($B$2:$B$196="elu")-($B$2:$B$196="fyu")-($B$2:$B$196="gru")-($B$2:$B$196="kiu")-($B$2:$B$196="*kou")-($B$2:$B$196="marelli")-($B$2:$B$196="mdu")-($B$2:$B$196="ncu")-($B$2:$B$196="pmu")-($B$2:$B$196="sfu")-($B$2:$B$196="sgu")-($B$2:$B$196="syu")$I$2:$I$196)

Thanks in advance,
Pam
 
Rather than using all those criteria, you might be able to modify your
wildcard idea to:
=SUMPRODUCT(--ISERROR(SEARCH("total",$B$2:$B$196)),$I$2:$I$196)
 
Luke,

Thanks for replying. If I only search for "total" it will still total
everything. The formula in my original post did not include those item
classes listed, but I need a total for all others that are not listed in the
formula. I'm not sure if this is clear.

Pam
 
to exclude items listed (such as DNU, dou, etc.), you could do this:

=SUMPRODUCT(--ISERROR(MATCH($B$2:$B$196,{"DNU","dou","elu","fyu","gru","kiu","kou","marelli","mdu","ncu","pmu","sfu","sgu","syu"})),$I$2:$I$196)

If you still need to also exclude lines with "total", combine formulas into
=SUMPRODUCT(--ISERROR(MATCH($B$2:$B$196,{"DNU","dou","elu","fyu","gru","kiu","kou","marelli","mdu","ncu","pmu","sfu","sgu","syu"})),--ISERROR(SEARCH("total",$B$2:$B$196)),$I$2:$I$196)
 
Luke,

The second one worked like a charm. Thank you again for the solutions
you've provided for me.

Pam
 
Luke,

I may have spoke too soon. It seemed to work with the sample data I tried
it on, but I can't get it to work on my whole spreadsheet. I keep getting
zero for the total. I've attached a portion of my spreadsheet for
clarification with column listings above each col header.

colA colB colI
SLSP CLS MERCH
BB dop $632.93
DOP Total $632.93

BB gru $220.00
BB GRU $7,675.00
BB GRU $1,650.00
BB GRU $1,425.00
BB GRU $1,190.00
BB GRU $10,450.00
GRU Total $22,610.00

Grand Total $23,242.93


Using your second formula:

=SUMPRODUCT(--ISERROR(MATCH($B$2:$B$196,{"DNU","dou","elu","fyu","gru","kiu","kou","marelli","mdu","ncu","pmu","sfu","sgu","syu"})),--ISERROR(SEARCH("total",$B$2:$B$196)),$I$2:$I$196)

it returns a zero (0). If I remove all matches except "gru", it gives total
for rows with dop, dop total and grand total. If I put in "DOP", it still
returns zero (0). Why total one and not the other?

Sumproduct, with all the other functions that can be combined with it, is
very confusing - iserror, isna, match, search, find.

I would really appreciate any help you can give me to make this work and to
understand what I'm doing wrong with sumproduct.

Thanks again,
Pam
 
After much research and trying different equations, this seems to work as
needed:

=SUMPRODUCT(--ISERROR(SEARCH("total",$B$2:$B$13)),--ISERROR(SEARCH("total",$A$2:$A$13)),--ISERROR(MATCH($B$2:$B$13,{"DNU","dou","elu","fyu","gru","kiu","kou","marelli","mdu","ncu","pmu","sfu","sgu","syu"},FALSE)),$I$2:$I$13)

Pam
 
Back
Top