sumifs with multiple ranges

Christina09

Hi there,

I'm stuck in finding the right formula for this, hope someone in here can help.

I have a master sheet(Demo Commission) with PO# and different agents getting different % of commsion. In the bottom sheet (Demo- John), it's John's commission in detail. It has different two columns, one for commission % and commission% which are share.

The column formula for sheet (Demo- John) column [Commission %]:

If share commission is not checked, it finds John's name in the agent(s) columns and add up the commissions that have John's name. If not, leave it blank, ""

The column formula for sheet (Demo- John) column [Commission Share %]:

If share commission is checked, it finds John's name in the agent(s) columns and add up the commissions that have John's name. If not, leave it blank, ""

*Note that the agent's name can appear in more than one row.

Thanks for your BIG help!!

Terri Leong

There's probably a more elegant way, but you can just use multiple SUMIFS statements.

The column formula for sheet (Demo- John) column [Commission %]: (share box not checked)

=SUMIFS({Demo Commission Commission Range}, {Demo Commission PO# Range}, [PO#]@row, {Demo Commission Agent Range}, "John", {Demo Commission Share Comission Range}, FALSE) + SUMIFS({Demo Commission Commission2 Range}, {PO# Range}, [PO#]@row, {Demo Commission Agent2 Range}, "John", {Demo Commission Share Comission Range}, FALSE) + SUMIFS({Demo Commission Commission3 Range}, {PO# Range}, [PO#]@row, {Demo Commission Agent3 Range}, "John", {Demo Commission Share Comission Range}, FALSE) + etc....

The column formula for sheet (Demo- John) column [Commission Share %]: (share box checked)

=SUMIFS({Demo Commission Commission Range}, {Demo Commission PO# Range}, [PO#]@row, {Demo Commission Agent Range}, "John", {Demo Commission Share Comission Range}, TRUE) + SUMIFS({Demo Commission Commission2 Range}, {PO# Range}, [PO#]@row, {Demo Commission Agent2 Range}, "John", {Demo Commission Share Comission Range}, TRUE) + SUMIFS({Demo Commission Commission3 Range}, {PO# Range}, [PO#]@row, {Demo Commission Agent3 Range}, "John", {Demo Commission Share Comission Range}, TRUE) + etc....

Good luck!

Terri Leong

There's probably a more elegant way, but you can just use multiple SUMIFS statements.

The column formula for sheet (Demo- John) column [Commission %]: (share box not checked)

=SUMIFS({Demo Commission Commission Range}, {Demo Commission PO# Range}, [PO#]@row, {Demo Commission Agent Range}, "John", {Demo Commission Share Comission Range}, FALSE) + SUMIFS({Demo Commission Commission2 Range}, {PO# Range}, [PO#]@row, {Demo Commission Agent2 Range}, "John", {Demo Commission Share Comission Range}, FALSE) + SUMIFS({Demo Commission Commission3 Range}, {PO# Range}, [PO#]@row, {Demo Commission Agent3 Range}, "John", {Demo Commission Share Comission Range}, FALSE) + etc....

The column formula for sheet (Demo- John) column [Commission Share %]: (share box checked)

=SUMIFS({Demo Commission Commission Range}, {Demo Commission PO# Range}, [PO#]@row, {Demo Commission Agent Range}, "John", {Demo Commission Share Comission Range}, TRUE) + SUMIFS({Demo Commission Commission2 Range}, {PO# Range}, [PO#]@row, {Demo Commission Agent2 Range}, "John", {Demo Commission Share Comission Range}, TRUE) + SUMIFS({Demo Commission Commission3 Range}, {PO# Range}, [PO#]@row, {Demo Commission Agent3 Range}, "John", {Demo Commission Share Comission Range}, TRUE) + etc....

Good luck!

Christina09

Thanks, @Terri Leong!! :)

That was super quick and simple, not sure why I'm stuck on it. LOL