Solved

# SUMPRODUCT with MULTIPLE Criteria

Posted on 2010-01-07
334 Views
Column G=Employ Number (G88:G15999)
Column C= Month
Column D = Equipment Type either A,B, or C
Column Q = \$ Sum of equipment sold

I want to Sum the Sales of model "A, B, or C' equipment sold in each month by employee number.

My formula =SUMPRODUCT(--(\$G\$88:\$G\$15999=\$C16),--(\$D88:\$D15999= A or B or C),--(\$C88:\$C15999>=Month Start),--(\$C88:\$C15999<=<Month End),\$Q\$88:Q\$15999

My issue is the second item and wanting it to search for A or B or C and in any one of those cases sum the total in Q88:Q15999 if all the other criteria are met as well.

Any ideas?

0
Question by:sbjmurrieta

LVL 10

Expert Comment

=SUMPRODUCT((\$G\$88:\$G\$15999=\$C16)*(\$D88:\$D15999= A or B or C)*(\$C88:\$C15999>=Month Start)*(\$C88:\$C15999<=<Month End)*(\$Q\$88:Q\$15999))
try that.
Change  A or B or C to a Cell reference that has the value
0

LVL 39

Expert Comment

you should be able to use

--not(isna(match(\$D88:\$D15999,{"A","B","C"},0)))

--(\$D88:\$D15999= A or B or C)

Thomas
0

LVL 39

Expert Comment

or as total formula:
=SUMPRODUCT((\$G\$88:\$G\$15999=\$C16)*not(isna(match(\$D88:\$D15999,{"A","B","C"},0)))*(\$C88:\$C15999>=Month Start)*(\$C88:\$C15999<=<Month End)*\$Q\$88:Q\$15999)
0

LVL 80

Expert Comment

I don't believe you need the NOT(ISNA(MATCH    as long as you can enter the actual name being sought instead of "A", "B" and "C" in the SUMPRODUCT

=SUMPRODUCT(--(\$G\$88:\$G\$15999=\$C16),--(\$D88:\$D15999= {"A","B","C"}),--(\$C88:\$C15999>=Month Start),--(\$C88:\$C15999<=<Month End),\$Q\$88:Q\$15999)
0

LVL 50

Expert Comment

Surely that won't work, Brad? If you use ={"A","B","C"} syntax then you need to use * between conditions rather than ,--

Personally I'd use Thomas' suggestion but instead of NOT(ISNA you can use ISNUMBER

Regards, barry
0

LVL 80

Expert Comment

sbjmurrieta,
Barry is correct--I used the * between conditions when I tested before posting. And sure enough, it doesn't work with the commas when I test right now.

You lose the ability to tolerate text in column Q with this formula, but it shows what I should have posted:
=SUMPRODUCT((\$G\$88:\$G\$15999=\$C16)*(\$D88:\$D15999= {"A","B","C"})*(\$C88:\$C15999>=Month Start)*(\$C88:\$C15999<=<Month End)*\$Q\$88:Q\$15999)

0

Author Comment

My "A,"B,"C is actually a list with dozens of possibilities.  WHw would the formula change i I changed from ext to search  range? For example:

=SUMPRODUCT((\$G\$88:\$G\$15999=\$C16)*(\$D88:\$D15999= {"A","B","C"})*(\$C88:\$C15999>=Month Start)*(\$C88:\$C15999<=<Month End)*\$Q\$88:Q\$15999)

=SUMPRODUCT((\$G\$88:\$G\$15999=\$C16)*(\$D88:\$D15999= {\$GW5:\$GW\$15})*(\$C88:\$C15999>=Month Start)*(\$C88:\$C15999<=<Month End)*\$Q\$88:Q\$15999)

Thank you!
0

LVL 50

Expert Comment

If your range to check is a column like yours (i.e. GW5:GW15) then the easiest way is to revert to Thomas' suggestion with MATCH, although I'd use ISNUMBER as indicated above, i.e.
=SUMPRODUCT((\$G88:\$G15999=\$C16)*ISNUMBER(MATCH(\$D88:\$D15999,\$GW5:\$GW15,0))*(\$C88:\$C15999>=Month Start)*(\$C88:\$C15999<=<Month End),\$Q88:\$Q15999)
regards, barry

0

Author Comment

When I use the formula you suggested above, it returns a "N/A." in each cell I put it in. Not sure what's wrong.

0

LVL 50

Accepted Solution

OK, I tested and found a typo with <=< and I assume Month End and Month Start are named ranges, in which case they can't have spaces so I changed to MonthStart and MonthEnd. I assume column C has dates?
I used this version in a test sheet and it gave me a result with some dummy data
=SUMPRODUCT((\$G88:\$G15999=\$C16)*ISNUMBER(MATCH(\$D88:\$D15999,\$GW5:\$GW15,0))*(\$C88:\$C15999>=MonthStart)*(\$C88:\$C15999<=MonthEnd),\$Q88:\$Q15999)
That formula is in M2 (shaded yellow) and criteria cells (included names ranges) are coloured red as are the rows that satisfy the criteria, see attached
regards, barry

25029396.xls
0

Author Closing Comment

Awesome support! These are true experts! Thank you!
0

## Featured Post

### Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.