Fomula to consolidate month total

[rizmomin]

Hello:

Please refer to attached file.
I have data in column H of sheet "Store1" and "STore2" for Date shown in Column G.

I need Formula in Sheet "Summary" in Cell B4 and B5 to give total for given month entered in cell A3.

Let me know if you have any questions.
Thanks.
Riz

[Richard Buttrey]

Hi,

One way in B4

Please Login or Register  to view this content.

but one has to ask why you are keeping this stuff on separate tabs. You'd be better advised to keep all data on one sheet and add an extra column to record the Store Reference. Spreading data across sheest makes subsequent analysis or summarisation more difficult.

[rizmomin]

Hello Richard:

Thank you but this adds all data. I need total of data for a given month (Cell A3 of Summary tab)

Please let me know if you have any questions.
Thanks.

Riz

[Richard Buttrey]

Before getting too far with this it seems to be a much simplified example and probably doesn't represent the size or layout of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Are you able to upload a real workbook?

That said you haven't commented on whether you are willing to change your layout. You really are complicating this since it will need three INDIRECT formulae incorporated in a SUMIFS function. YOU could simplify it by having a monthly summary table on each sheet in exactly the same position. That would make it easier for the Summary table to pick the relevant months total.

But, and I can't stress this enough, you are really storing up difficulties with this method of handling data. Put it all on a single sheet and everything is simplified.

[rizmomin]

Hello Richard:

I understand exactly what you are refering to.
But my situation is requiring the way i have mentioned.
I can explain.
I have quite a few business Payroll information for each pay period coming in seperate business name sheets.
Now the reason of my Summary tab with month requirement is to show and file/pay payroll taxes by printing out one Summary sheet to make it easy.

I hope this is clear.

Please let me know if you have any questions.
Thanks.

Riz

Note : Richard, i will try to SUmmarize Monthly in each of the sheet and that should make it easy.
Thanks a lot

[rizmomin]

Hello Richard:

Thanks a lot for your suggestion and i was able to resolve the issue.

Riz