Multiple Criteria for Sumproduct
 

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

Multiple Criteria for Sumproduct
 

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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Pam" wrote:

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

Multiple Criteria for Sumproduct
 

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" wrote in message
...
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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Pam" wrote:

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

Multiple Criteria for Sumproduct
 

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","sf u","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","sf u","sgu","syu"})),--ISERROR(SEARCH("total",$B$2:$B$196)),$I$2:$I$196)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Pam" wrote:

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" wrote in message
...
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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Pam" wrote:

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

Multiple Criteria for Sumproduct
 

Luke,

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

Pam

"Luke M" wrote in message
...
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","sf u","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","sf u","sgu","syu"})),--ISERROR(SEARCH("total",$B$2:$B$196)),$I$2:$I$196)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Pam" wrote:

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" wrote in message
...
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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Pam" wrote:

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

Multiple Criteria for Sumproduct
 

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","sf u","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

"Luke M" wrote in message
...
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","sf u","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","sf u","sgu","syu"})),--ISERROR(SEARCH("total",$B$2:$B$196)),$I$2:$I$196)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Pam" wrote:

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" wrote in message
...
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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Pam" wrote:

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

Multiple Criteria for Sumproduct
 

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

"Luke M" wrote in message
...
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","sf u","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","sf u","sgu","syu"})),--ISERROR(SEARCH("total",$B$2:$B$196)),$I$2:$I$196)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Pam" wrote:

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" wrote in message
...
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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Pam" wrote:

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