SUMIF & SUMIFS file size is ridiculous

[Aaron Young]

Hi folks

In part, some of this issue has been covered by prior posts, but I am starting this new thread for the specifics of this instance.

I have a file that is 17.7 MB in size, regardless of how much data winds up being stored in it, which I have been adding to each week since I created it 4 weeks ago.

There are 2 tabs:
The first is for the raw data that has one minor multiplication (eg: =F82*G82 for a kilograms by dollar rate to produce a value for that row) per row for 81 rows. The remainder of each row is 7 or 8 cells of very simple data. Of these, three of them are referenced in the second tab.
What is now 81 rows was originally only 4, but has increased each week.

The second is a summarising calculation page. In one column there are currently 26 cells that contain a SUMIFS formula referencing a unique identifier in a preceding column which target the three columns in the first tab from Row 1 to 500 like this [=SUMIFS(Data!$H$1:$H$500,Data!$A$1:$A$500,A3,Data!$C$1:$C$500,$F$1)]
What is now 26 instances was originally only 9, but has increased each week.
Additionally, there is a single SUMIF formula that calculates the total dollars from the Data tab if a condition is met in Column C [=SUMIF(Data!C2:C500,F1,Data!H2:H500)], and an addition of the values that occur from the 26 instances of SUMIFS queries.

On each occasion, the file size has been 17.7 MB which seems way too high for such a small range of data and formulae that are restricted to only the first 500 rows.

Can anyone tell me why it is so big? I have much more complex worksheets that have multiple instances for many more columns and rows that is only 25-30 MB, and while they frustrate me on occasion, too, I understand the file size and can tolerate it since they do such a massive amount of calculation work. This file is a pipsqueak, yet is bulkier than them based on function!

Cheers!

 

[Jon von der Heyden]

Lets start with something simple. Do this for each sheet in the workbook.

1. Activate the sheet
2. Go to the VBE (ALT+F11)
3. Go to the Immediate window (Control+G)
4. Type: ?ActiveSheet.UsedRange.Address
5. Hit ENTER
6. Report back on the range that results.

 

[kchiba]

Try this, hit Ctrl End on each sheet, this should take you to the last used cell on the sheet.

If the result is beyond the actual range you should Delete extra the Columns and/or Rows

Another problem could be the extensive use of formatting, this usually blows out the size of the file

 

[Vishal Srivastava]

Hi folks

In part, some of this issue has been covered by prior posts, but I am starting this new thread for the specifics of this instance.

I have a file that is 17.7 MB in size, regardless of how much data winds up being stored in it, which I have been adding to each week since I created it 4 weeks ago.

There are 2 tabs:
The first is for the raw data that has one minor multiplication (eg: =F82*G82 for a kilograms by dollar rate to produce a value for that row) per row for 81 rows. The remainder of each row is 7 or 8 cells of very simple data. Of these, three of them are referenced in the second tab.
What is now 81 rows was originally only 4, but has increased each week.

The second is a summarising calculation page. In one column there are currently 26 cells that contain a SUMIFS formula referencing a unique identifier in a preceding column which target the three columns in the first tab from Row 1 to 500 like this [=SUMIFS(Data!$H$1:$H$500,Data!$A$1:$A$500,A3,Data!$C$1:$C$500,$F$1)]
What is now 26 instances was originally only 9, but has increased each week.
Additionally, there is a single SUMIF formula that calculates the total dollars from the Data tab if a condition is met in Column C [=SUMIF(Data!C2:C500,F1,Data!H2:H500)], and an addition of the values that occur from the 26 instances of SUMIFS queries.

On each occasion, the file size has been 17.7 MB which seems way too high for such a small range of data and formulae that are restricted to only the first 500 rows.

Can anyone tell me why it is so big? I have much more complex worksheets that have multiple instances for many more columns and rows that is only 25-30 MB, and while they frustrate me on occasion, too, I understand the file size and can tolerate it since they do such a massive amount of calculation work. This file is a pipsqueak, yet is bulkier than them based on function!

Cheers!

Hi,
You can also save your workbook in Binary format i.e. *.xlsb.

Size for your workbook would be small enough to handle.


Thanks & Regards,
CMA Vishal Srivastava

 

[rumshar]

What is your file format? xls or xlsx? I have noticed xls's size is double than xlsx. Also if you multiple sheets your filesize baloons. Even with just 2 used sheets,file size doubles.Size increases with live pivot table(s) and conditional fomatting, formulas, charts etcs.

 

[Aaron Young]

Thanks for your replies!
OK, I will try and give you all the answered you wanted:
First, John von der Heyden...the response I received was: $A$1:$F$28
Second, kchiba, the first tab (Data) goes all the way down to the last available cell in Column I, and I suspect this may be due to formatting issues such as centering and/or date formats on the column and so forth.
Third, Vishal Srivastava, I will give it a shot as a separate file and see what happens.
And fourth, rumshar, it is xlsx format. I know what you are saying in that the old xls format were larger file sizes, and I used to have some Whopper sized files back then (one was 70+ MB and taxed my system resources every time I opened it or it autosaved!).
Cheers all! Any addition advice is appreciated.

 

[Aaron Young]

Vishal Srivastava: a binary version worked well! 17.7 MB became 2.86 MB, so I will continue to use it.
For curiosities sake, does anyone have a preference for these circumstances (xlsx or xlsb) as to which you prefer to use? Do binary functions actually prevent macros from working, etc.?
Thanx!

 

[rumshar]

Aaron,
Check to see if you have any unseen text box like objects in any of your sheet. If your spreadsheet responds very slowly , I am sure you have those tiny objects.'GoTo Special' command should help you, if any, to get rid of them. It has happened many times with me.
With Regards
Rudra