Multiple Criteria for Sumproduct

[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

 

[Luke M]

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)

 

[Pam]

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

 

[Luke M]

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)

 

[Pam]

Luke,

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

Pam

 

[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

 

[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