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.

=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

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

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!

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:

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.

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

try that.

Change A or B or C to a Cell reference that has the value