try that.

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

Solved

Posted on 2010-01-07

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?

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$15

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?

11 Comments

try that.

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

--not(isna(match($D88:$D15

instead of

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

Thomas

=SUMPRODUCT(($G$88:$G$1599

=SUMPRODUCT(--($G$88:$G$15

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

Regards, barry

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$1599

Brad

=SUMPRODUCT(($G$88:$G$1599

=SUMPRODUCT(($G$88:$G$1599

Thank you!

=SUMPRODUCT(($G88:$G15999=

regards, barry

I used this version in a test sheet and it gave me a result with some dummy data

=SUMPRODUCT(($G88:$G15999=

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

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Path of Workbook | 3 | 30 | |

IF OR formula Excel | 8 | 22 | |

Simple Calculation for Value of Availablity | 5 | 48 | |

excel totals from one column based on 'not null cell' in a different column | 7 | 0 |

This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**13** Experts available now in Live!