Is Sumif best solution here?

[dariushou]

Hello everyone,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>--best solution in terms of speed</o>
<o>--using excel 2007 & xp64</o>
<o></o>
<o></o>I’m having a little problem or maybe no problem and I’m just delusional. I have a simple sumif in columns C through BC from rows 6-2927. The range it searches is column A with dates 1/1/07 – 12/31/14. When the criterion is matched, it adds up the related numerical cells in column B.

This is just a simplified example of what I’m working on, but when I perform a full workbook calculation on this example it takes approximately 7 seconds to calculate. The criteria for each column is just a hard coded date I have above each column. The real workbook is a bit more complex, but this example demonstrates my frustration which is the time it takes to calculate those 154,866 sumif over a range that is 2,922 rows. I have 8GB ram so I’m not lacking there. So maybe this is just the amount of time it takes and I just want to make sure there’s not a faster way.
<o></o>
Because I’m doing monthly, quarterly and annual summaries off of the daily data, I thought sumif would be the best approach, but not sure now. The dates I’m performing the sumif off of change based on a start and end date and whether the user selects, daily, monthly, quarterly or annual.
Maybe I should have a helper worksheet that has a running balance in column B so that the data is always summing up prior periods. So 1/2/07 would include be a sum of 1/1/07 and 1/2/07. Then just do a vlookup by date then minus the prior period. That may get tricky though as I’m going to have to tell it which date to use for the end date for each period. For example, if doing monthly and when it comes to July. What daily date in July do you vlookup (7/31/07, but how do I tell it that). Once I have the vlookup then you just subtract the prior vlookup you performed for June.

<o></o>
Any ideas? In time the spreadsheet will only get bigger, but then again so will faster computers so if I can get this down a little bit more as for speed, I think I will be good to go.
<o></o>
Thanks,
Darius

 

[MrKowz]

Have you considered using a PivotTable to summarize the data?

 

[dariushou]

I have not. I don't know much about pivot tables to be honest. The data will feed a dynamic chart so i'm not sure if that is possible with pivot tables.

Also, the data that I currently perform the sumif calcs on is over 50+ columns (although any one sumif is on just one of those 50+ columns). I think using a pivot table all data would have to be included and then i would select date ranges, etc.. using the pivot table. I'm willing to learn, but does it make sense you think. I know very little about pivot tables.

 

[MrKowz]

Pivot Tables can be used to create Pivot Charts as well. I'd recommend looking into them. They are incredibly easy to set up through the Pivot Table wizard, and very intuitive.

 

[Jinjunwei]

I'm gonna second the pivit table idea. I was actually trying to do a sumif type scenario yesterday and like you, was not as familular with pivittables as I should be. They work really well. When you mean dynamic date, do you mean a live feed (like bloomberg)?

 

[dariushou]

Dynamic being if the user selects Monthly the dates would be jan 07, feb 07, etc...instead of daily. The dates change and thus the sumif results change. However, it looks like i'm going to give the pivot tables a look.

Thanks for your help. I've put off pivot tables long enough...

Darius