• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

SUMPRODUCT with MULTIPLE Criteria

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
sbjmurrieta
Asked:
sbjmurrieta
  • 3
  • 3
  • 2
  • +2
1 Solution
 
bromy2004Commented:
=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
 
nutschCommented:
you should be able to use

--not(isna(match($D88:$D15999,{"A","B","C"},0)))
 
instead of
--($D88:$D15999= A or B or C)

Thomas
0
 
nutschCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
byundtCommented:
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
 
barry houdiniCommented:
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
 
byundtCommented:
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)

Brad
0
 
sbjmurrietaAuthor Commented:
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
 
barry houdiniCommented:
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
 
sbjmurrietaAuthor Commented:
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
 
barry houdiniCommented:
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
 
sbjmurrietaAuthor Commented:
Awesome support! These are true experts! Thank you!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now