SUMIF very slow - Excel 2008 for Mac

[mtown02]

Hello All,

I am brand new here, but have gotten great advice here for years! I was hoping that you could tell me if there is something wrong with my machine or the way that I am going about my calculations.

First, I run Excel 2008 for Mac...my iMac is a 2.66 Ghz Intel and 4G memory. So, VBA isnt really an option for me...

My data is 17,000 rows of hours worked and wages earned in a given year. I have to break this out by year resulting in one record for each individual, rather than one record per year for each individual.

I have 5 years worth of this data in the 17,000 rows. I have been using the SUMIF function in the resulting 10 columns needed (5 years of hours worked and 5 years of wages earned). Due to multiple years worked for people, I end up with 12,000 unique individuals represented.

My formula took on the basic structure: SUMIF(SSN Range,SSN,Wages Earned)

This process just took my machine 1.5 hours to perform and took 100% of my processor capacity and about half of the memory. Results were completely accurate, but SOOO SLOOOW....

10 columns x 12,000 rows = 120,000 formulas!

Is this lengthy processing time normal?

Much thanks in advance!!

 

[Jonmo1]

Welcome to the board..

Perhaps a pivot table would be better?

Also, are your ranges using Entire Column Refs like A:A ?
If so, that could be a source of slowness.

You might consider using dynamic named ranges
http://www.cpearson.com/Excel/excelF.htm#DynamicRanges

 

[mtown02]

Welcome to the board..

Perhaps a pivot table would be better?

Also, are your ranges using Entire Column Refs like A:A ?
If so, that could be a source of slowness.

You might consider using dynamic named ranges
http://www.cpearson.com/Excel/excelF.htm#DynamicRanges

This calculation is merely the first (yet most complicated) of a series of other calculations that must be performed on the resulting data set.

Would a pivot table allow me to perform further calculations on that result? I have very little experience with them.

Thanks!

 

[Jonmo1]

Yes, you can do calculations on the data resulting from a pivot table.

 

[mtown02]

Welcome to the board..

Perhaps a pivot table would be better?

Also, are your ranges using Entire Column Refs like A:A ?
If so, that could be a source of slowness.

You might consider using dynamic named ranges
http://www.cpearson.com/Excel/excelF.htm#DynamicRanges

Went back and reviewed the formulas again...there were two Entire Column Refs...the lookup range as well as the sum range...I guess I used them because it was easier to write the formula that way...my machine just did the same calcs in 5 min that it took 1.5 hours before...I had no idea that a small change would impact it that way...WOW!

Thanks so much!!

 

[Jonmo1]

Glad to help, thanks for the feedback..

Do you have alot of Vlookup formulas as well?
Those can likely be improved too.