Total for similiar dates

[ctrapper]

I have a worksheet with 30 tabs, each tab contains a list of date in Column A, none of them are repeating, but they are found in the other tabs, what's the the best way to total (on a seperate tab) for each date? Bare bones example. Say on all 30 tabs May 25th is listed in Column A, and there is a $$ amount in Column B. I want to go to a new tab, and say what is the total $ amount for all May 25ths? I want to do this for about 5 months worth of dates so is a copy down possible?

[NBVC]

How are your sheettabs named?

[ctrapper]

Sorry they are tabbed with with City abbreviations, example STL, NY, TB.. etc

[NBVC]

Well there are a couple of ways.. the easiest is to download a free addin from here: Morefunc.xll and use formula like so:

Please Login or Register  to view this content.

where STL is left most lookup sheet and TB is right-most and A1:B100 is your lookup range.

Alternatively, remaining with native Excel, another method would be:

List all your tab names in a column and name that range, TabNames via Insert|Name|Define

Then use formula like:

Please Login or Register  to view this content.

[ctrapper]

Ok cool! Let me try download the Addin and I will play around with it, if not I will try the native excel version! Thanks for the help!

[ctrapper]

Hi NBVC.. couple of things. I downloaded the morefunc file, and used the formula you provided, I did get data back, but I can't figure out where the calculation came from. So I cleaned up my spreadsheet to make sure each "tab" was formatted exaclty the same. I also had to add some columns. So the Dates are still in Col A starting with A7 going down, Data I want calculated is in Col N stating in N7 going down. Is it a problem if the data is not together, i.e. Col A= Dates, Col B= Numbers? Also, on some tabs, a specific date may not contain data, or number, does that mess up the formula? Do I need to go into each tab, and enter 0, if the cell is blank or should that matter? One last item. I have approx 30 of these tabs, starting with ATL end with WAS, I am putting this formula in a new tab after the WAS tab, is that the correct way to do this? Thanks for you help.. oh shoot one more item... the dates are not the same on all Tabs.. i.e. ATL may go 4/1, 4/2, 4/3 and PHI may go 4/1, 4/3 etc, does that cause issues?

[NBVC]

Your setup should work...

So in your new tab, assuming the date to look up also starts at A7, the formula should be:

=SUMPRODUCT(--(THREED('ATL:WAS'!$A$7:$A$1000)=A7),THREED('ATL:WAS'!$N$7:$B$1000))

You may need to change the 1000 to match the bottom of your ranges.

[ctrapper]

shoot still having issues.. let me throw togehter an example and upload it.. maybe I not describing something correclty.. I am getting 0's back..

[NBVC]

Before you do that, try:

I forgot to change the last B1000 to N1000...


=SUMPRODUCT(--(THREED('ATL:WAS'!$A$7:$A$1000)=A7),THREED('ATL:WAS'!$N$7:$N$1000))

remember to change the 1000's if necessary.

[ctrapper]

OK tab 5 explains what I am trying to do.. thanks in advance for your help!

[NBVC]

Your dates in tab 5 are all 2010 dates, while the other tabs are all 2009 dates.

Change the tab 5 dates to 2009 dates, then apply formula:

=SUMPRODUCT(--(THREED(ATL:WAS!$A$7:$A$1000)=A2),THREED(ATL:WAS!$N$7:$N$1000))

copied down.

[ctrapper]

awesome! I didn't even think about that! Thanks a ton!