Sum Across Multiple Worksheets with Two Criteria

asalman07

Active Member
Joined
Jun 12, 2013
Messages
325
Hello everyone,

I have 5 worksheet tabs in my workbook. Their respective names are:

1. Total
2. 101
3. 209
4. 408
5. 708

The formula that I currently have that is giving me a #VALUE! is
SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList&"'!B$18:B$23"),INDIRECT("'"&SheetList&"'!$A$18:$A$23"),$A4,INDIRECT("'"&SheetList&"'!$B$17:$N$17"),B$3))

My problem is the last part of this formula. My range is B17:N17 which are all dates . The worksheet tabs 101, 209, 408, and 708 are not identically set up regarding range B17:N17. For example in tab 101 Cell B17 has a date of Mar-13 while in tab 209 Cell B17 has a date of Sep-13.

I am sure there is a way to solve this,

This is how my Total Tab looks:

Oct-13Nov-13Dec-13Jan-14Feb-14Mar-14Apr-14May-14Jun-14Jul-14Aug-14Sep-14
Rent#VALUE!
Principal and Interest
Property Taxes
Maintenance
Special Assessment
Profit/Loss

<colgroup><col><col><col span="11"></colgroup><tbody>
</tbody>


Thank you
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Assuming that the sheet names are listed in a one dimensional vertical (not horizontal) range of cells, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(SUMIF(INDIRECT("'"&SheetList&"'!A18:A23"), $A4,OFFSET(INDIRECT("'"&SheetList&"'!B18:N23"),,MMULT(IF(N(OFFSET(INDIRECT("'"&SheetList&"'!B17:N17"),,{0,1,2,3,4,5,6,7,8,9,10,11,12},,1))=B$3,{0,1,2,3,4,5,6,7,8,9,10,11,12},0),{1;1;1;1;1;1;1;1;1;1;1;1;1}),,1)))

Hope this helps!
 
Upvote 0
Hi Domenic,

Thank you for the reply. I tried your formula. I don't think it is summing correctly.

Maybe it has something to do with my sheet names. I am providing a link to my workboook through screencast.

For example, for the month of Oct-13 the desired result should be 3100.

Thanks again it is a nice formula you developed. Thanks for your time

<a href="http://www.screencast.com/t/HgR5sSWyF2c">Apartments</a>
Assuming that the sheet names are listed in a one dimensional vertical (not horizontal) range of cells, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(SUMIF(INDIRECT("'"&SheetList&"'!A18:A23"), $A4,OFFSET(INDIRECT("'"&SheetList&"'!B18:N23"),,MMULT(IF(N(OFFSET(INDIRECT("'"&SheetList&"'!B17:N17"),,{0,1,2,3,4,5,6,7,8,9,10,11,12},,1))=B$3,{0,1,2,3,4,5,6,7,8,9,10,11,12},0),{1;1;1;1;1;1;1;1;1;1;1;1;1}),,1)))

Hope this helps!
 
Upvote 0
ADVERTISEMENT
There are a few issues here that need to be addressed...

1) The formula needs to be adjusted to take into consideration situations where a sheet may not contain a column for a particular month.

2) The array formula first needs to be entered in cell B4. Once entered, it can then be copied across and down to your other cells.

3) The dates don't seem to be consistent. And one of the dates is not a true date, but rather a text value.

Therefore, in view of the above, first you'll need to adjust the dates. You can easily do this by selecting sheets 101, 209, 408, and 708 so that they're grouped. You can select them by first clicking on the sheet tab for 101, and then while the Shift key is pressed down, click on the sheet tab 708. Once grouped, enter the following formula in B17...

=DATE(YEAR(B6),MONTH(B6),1)

This will give you the first day of the month and year, based on the date in B6, which I believe is what you want based on your existing formula. Then enter the following formula in C17, and copy across...

=DATE(YEAR(B17),MONTH(B17)+1,1)

Now, in your summary sheet, enter the following array formula in B4, and confirm with CONTROL+SHIFT+ENTER...

=SUM(IF(COUNTIF(INDIRECT("'"&SheetList&"'!B17:N17"),B$3)>0,SUMIF(INDIRECT("'"&SheetList&"'!$A$18:$A$23"),$A4,OFFSET(INDIRECT("'"&SheetList&"'!$B$18:$B$23"),,MMULT(IF(N(OFFSET(INDIRECT("'"&SheetList&"'!B17:N17"),,{0,1,2,3,4,5,6,7,8,9,10,11,12},,1))=B$3,{0,1,2,3,4,5,6,7,8,9,10,11,12},0),{1;1;1;1;1;1;1;1;1;1;1;1;1}),,1))))

If entered correctly, Excel will automatically place braces around the formula. Lastly, copy the formula across and down.

Hope this helps!
 
Upvote 0
Hi Domenic,

I just woke up to this. Simply beautiful. I can not thank you enough. Thanks for the time you spent on this and explaining this in such a nice way. Amazing stuff!!

Thanks!

There are a few issues here that need to be addressed...

1) The formula needs to be adjusted to take into consideration situations where a sheet may not contain a column for a particular month.

2) The array formula first needs to be entered in cell B4. Once entered, it can then be copied across and down to your other cells.

3) The dates don't seem to be consistent. And one of the dates is not a true date, but rather a text value.

Therefore, in view of the above, first you'll need to adjust the dates. You can easily do this by selecting sheets 101, 209, 408, and 708 so that they're grouped. You can select them by first clicking on the sheet tab for 101, and then while the Shift key is pressed down, click on the sheet tab 708. Once grouped, enter the following formula in B17...

=DATE(YEAR(B6),MONTH(B6),1)

This will give you the first day of the month and year, based on the date in B6, which I believe is what you want based on your existing formula. Then enter the following formula in C17, and copy across...

=DATE(YEAR(B17),MONTH(B17)+1,1)

Now, in your summary sheet, enter the following array formula in B4, and confirm with CONTROL+SHIFT+ENTER...

=SUM(IF(COUNTIF(INDIRECT("'"&SheetList&"'!B17:N17"),B$3)>0,SUMIF(INDIRECT("'"&SheetList&"'!$A$18:$A$23"),$A4,OFFSET(INDIRECT("'"&SheetList&"'!$B$18:$B$23"),,MMULT(IF(N(OFFSET(INDIRECT("'"&SheetList&"'!B17:N17"),,{0,1,2,3,4,5,6,7,8,9,10,11,12},,1))=B$3,{0,1,2,3,4,5,6,7,8,9,10,11,12},0),{1;1;1;1;1;1;1;1;1;1;1;1;1}),,1))))

If entered correctly, Excel will automatically place braces around the formula. Lastly, copy the formula across and down.

Hope this helps!
 
Upvote 0
You're very welcome! I'm glad I could help!

Cheers!
 
Upvote 0

Forum statistics

Threads
1,196,463
Messages
6,015,396
Members
441,890
Latest member
Liero131313

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top