SUM Cells in Respective Year/Month

[Karen615]

Hello,



I need some help with formulas. I would like to be able to SUM up the following in the Total EOM sheet tab.

The cells that will be referenced are:
E12 in CDs tab
E12 in IRAs tab
E12 in Savings-Checking-Money Market tab
E2 in Annuity tab

I would like to be able to SUM all the above referenced cells at the end of each month in the Total EOM sheet tab in their respective year/month cells.

Would someone please help? Thank you for your consideration.

Happy New Year,
Karen

[macropod]

It's not apparent from your workbook where the date ranges on your various tabs are supposed to come from. One can guess that they're in column C for the CDs & IRAs tabs, but there's nothing equivalent on the Savings-Checking-Money Market tab or the Annuity tab.

Assuming column C is to be used for the CDs & IRAs tabs, a workable formula for those in B2 of Total EOM worksheet might be:

=SUMPRODUCT((CDs!$C$1:$C$12>DATE(B$1,ROW()-1,0))*(CDs!$C$1:$C$12<=DATE(B$1,ROW(),0)),CDs!$E$1:$E$12)+SUMPRODUCT((IRAs!$C$1:$C$12>DATE(B$1,ROW()-1,0))*(IRAs!$C$1:$C$12<=DATE(B$1,ROW(),0)),IRAs!$E$1:$E$12)

which can be copied down to row 13 and across to column L.

[Pecoflyer]

@Karen
It makes things easier for helpers when one provides some data and manually mocked-up results to his sheet
Why not use one sheet instead of the three first, just adding a column with their respective origin ? From there a simple Pivot Table would be the way to go.

[Pecoflyer]

Did the provided answers help?

[Karen615]

Yes, thank you!