Sumproduct w/multiple criteria including a division calculation

**cedarhill** · 05-02-2010, 11:34 AM

Excel 2003

See attached sample spreadsheet where I am attempting to calculate a commission for an account manager based on multiple selection criteria. This sheet is summarizing data in another sheet. I finally gave up.

I've included the logic path I need to follow in the sheet where the formula goes. Would appreciate any help or advice.

**daddylonglegs** · 05-02-2010, 12:39 PM

Not sure which value needs to be < 3, is that col I/col H?

"Multiple agreement total trucks"? don't see that either......

Examples always help - what result do you expect in B3 for the data you provided?

**cedarhill** · 05-02-2010, 12:45 PM

Sorry...my bad!

Yes...Col I / Col H

Should have said "Total Units" instead of Total Trucks

It should pick up on the 2nd item in the list (18 / 6 = 3) and multiply the Total Units (6) by $25.

Sorry I wasn't clearer.

**daddylonglegs** · 05-02-2010, 01:03 PM

Well, this formula should work for your data

=SUMPRODUCT(('Agreement Log'!$B$3:$B$9=$L$1)*(TEXT('Agreement Log'!$E$3:$E$9,"mmm")=B$2)*('Agreement Log'!$K$3:$K$9={"G","S"})*('Agreement Log'!$I$3:$I$9/'Agreement Log'!$H$3:$H$9<=3)*'Agreement Log'!$H$3:$H$9)*25

but note that if any value in the column I range is zero or blank then this will fail because of the #DIV/0! error. If you want to allow that possibility you could use a similar array formula like this

=SUM(IF('Agreement Log'!$I$3:$I$10>0,IF('Agreement Log'!$B$3:$B$10=$L$1,IF(TEXT('Agreement Log'!$E$3:$E$10,"mmm")=B$2,IF('Agreement Log'!$K$3:$K$10={"G","S"},IF('Agreement Log'!$I$3:$I$10/'Agreement Log'!$H$3:$H$10<=3,'Agreement Log'!$H$3:$H$10))))))*25

That formula would need to be confirmed with CTRL+SHIFT+ENTER