SUMIFS across multiple tabs

[Chinoy]

=SUMPRODUCT(SUMIFS(INDIRECT("'"&{"SalesRep1","SalesRep75"}&"'!$B$6"),$C5,(INDIRECT("'"&{"SalesRep1","SalesRep75"}&"'!D:D"),$B$1,(INDIRECT("'"&{"SalesRep1","SalesRep75"}&"'!O:O,L:L")))))

Hi, my formula above is not working. I have a workbook with 75 tabs, each representing a sales rep on my team. The formula sits on a tab at the end of the row that is to sum the amounts for accrual purposes. B6 is the Entity from each reps sheet C5 is the same Entity on the Accrual tab.

D:D is the column from each reps sheet that shows the date a deal was booked. B1 is the date on my accrual tab that i wanted to anchor to in order to state in the formula that I want to pull the amounts in Col O and L (new and renewal commission amounts respectively) for a particular month.

The formula does not return an error, but also is not pulling data from the sales rep tabs.

Please help!

Thank!

 

[SirJB7]

Hi, Chinoy!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Despite of this the SUMPRODUCT(SUMIFS combination doesn't sound correct to me, but without a file with sample data it's not so easy to check it... and why build that file if you already have it, don't you?

Regards!

 

[Chinoy]

Here is a sample from my workbook. Sorry I had to trim so much data as this is a very large file and it would not upload.

 

[SirJB7]

Hi, Chinoy!
The problem arises when trying to use 3D references across worksheets as only SUM function can handle them but neither SUMIF nor SUMIFS nor SUMPRODUCT.
Check this link from MS:
https://office.microsoft.com/en-us/...range-on-multiple-worksheets-HP010342355.aspx
Regards!

 

[Haseeb A]

Hello Chinoy,

Assuming you are looking to SUM both columns, L & O

Enter all your tab names in a range & define a name for the range, eg: TabList

Then try this formula.

=SUMPRODUCT(SUMIFS(OFFSET(INDIRECT("'"&TabList&"'!L:L"),,{0,3}),INDIRECT("'"&TabList&"'!B:B"),$C5,INDIRECT("'"&TabList&"'!D:D"),$B$1))

 

[SirJB7]

@Haseeb A
Hi!
Nice trick that of the named range for the worksheets, it avoids the array issue turning it into a range, doesn't it?
Regards!

 

[aaronmj0728]

Hello Chinoy,

Assuming you are looking to SUM both columns, L & O

Enter all your tab names in a range & define a name for the range, eg: TabList

Then try this formula.

=SUMPRODUCT(SUMIFS(OFFSET(INDIRECT("'"&TabList&"'!L:L"),,{0,3}),INDIRECT("'"&TabList&"'!B:B"),$C5,INDIRECT("'"&TabList&"'!D:D"),$B$1))

I'm using a similar formula to also sum-up multiple criteria across multiple tabs. However, I need to do this for more than just one cell across the tabs. When I try to drag my formula over to an adjacent cell, the cell reference doesn't change even though the cell (B6) isn't frozen. Why does the cell reference not change? Is there a way to adjust the formula so I can copy it to other cells?
=SUMPRODUCT(SUMIFS(INDIRECT("'"&Tabs&"'!B6"),INDIRECT("'"&Tabs&"'!$Q$1"),"Non-Saleable",INDIRECT("'"&Tabs&"'!$Q$2"),"PLS",INDIRECT("'"&Tabs&"'!$Q$3"),"T1"))

 

[Haseeb A]

Hello Aaron,

B6 is used here within "", so excel will think this is a TEXT not a cell reference.

One way is to use CELL function, ie CELL("address",B6) in place of B6 without quotes. So when ever you copy down, up, left or right will adjust accordingly.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Tabs&"'!"&CELL("address",B6)),INDIRECT("'"&Tabs&"'!$Q$1"),"Non-Saleable",INDIRECT("'"&Tabs&"'!$Q$2"),"PLS",INDIRECT("'"&Tabs&"'!$Q$3"),"T1"))

Red highlighted is the changes.

 

[aaronmj0728]

Hello Aaron,

B6 is used here within "", so excel will think this is a TEXT not a cell reference.

One way is to use CELL function, ie CELL("address",B6) in place of B6 without quotes. So when ever you copy down, up, left or right will adjust accordingly.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Tabs&"'!"&CELL("address",B6)),INDIRECT("'"&Tabs&"'!$Q$1"),"Non-Saleable",INDIRECT("'"&Tabs&"'!$Q$2"),"PLS",INDIRECT("'"&Tabs&"'!$Q$3"),"T1"))

Red highlighted is the changes.

Worked perfectly, thanks!!

 

[lina]

Hello Aaron,

B6 is used here within "", so excel will think this is a TEXT not a cell reference.

One way is to use CELL function, ie CELL("address",B6) in place of B6 without quotes. So when ever you copy down, up, left or right will adjust accordingly.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Tabs&"'!"&CELL("address",B6)),INDIRECT("'"&Tabs&"'!$Q$1"),"Non-Saleable",INDIRECT("'"&Tabs&"'!$Q$2"),"PLS",INDIRECT("'"&Tabs&"'!$Q$3"),"T1"))

Red highlighted is the changes.

Hi Hasseb, do you have a sample in an actual spreadsheet?