The use of SUMIFS over a large range with many empty cells leads up to bulky file?

[AVM]

I was wondering if extensive use of the SUMIFS function like this =SUMIFS(Transactions!$F$5:$F$1048576, Transactions!$C$5:$C$1048576, "="&A7, Transactions!$A$5:$A$1048576, "<="&$D$2) leads up to a bulky file?

Some backgroup info is: I use a journal of entries and I constantly have to add new entries, new rows in the journal - so it is easier for me to use as a range in a sum if the column till the very bottom (1mln cells - but they are mostly not filled) I do not want to change the range of the sumifs function every time I add new entries, because I have many sumifs like the above. I now see my file is bulky - 3.5MB. I did tried physically deleting the unused rows - but this did not make the file smaller. Any advice?

[Reapz]

Hello AVM.

Having a long formula over a lot of cells will eventually lead to a large file size and slow performance - as you are seeing. Perhaps you could upload a sample of the file and a short description so we can look at it for you?! There might be another way to perform the task without as much code.

[Fotis1991]

Even that i believe that IS NOT the SUMIFS function that creates the problem, the use of dynamic ranges in your formulas should helps you.

[AVM]

REAPZ: Thank you for the offer to take a look. Basically, not sure if a made-up file would bring more light to the issue than I already described. basically, there are about 700 rows and I have to add more and more rows and use sumifs on the journal. I do not want to change the range in the sumifs as there are many sumifs - so I would prefer to have big enough range - so I used ranges like this $F$5:$F$1048576. I am not sure if this very thing leads up to the bulkiness and I am trying to address the bulkiness issue. Deleting entry rows and columns did not help

[AVM]

FOTIS1991: What would the use of dynamic range help?

[romperstomper]

For your example formula I'd use whole column references:
=SUMIFS(Transactions!$F:$F, Transactions!$C:$C, "="&A7, Transactions!$A:$A, "<="&$D$2)

The only way the formulas should impact the file size is by their length, not the cells they refer to.

[Fotis1991]

Dynamic ranges use only the cells with data(until last row with data).

@Rory

We don't know what Op has in rows 1:4(s)he starts the references from row 5.

[AVM]

ROMPERSTOMPER: Am I correct in understanding you that the fact that the formula refer to a million of cells does not mean that excel has to store the empty cells?

On a side note, if I define DATES = OFFSET(Transactions!$A$5, 0, 0, LastRow, 1), how could I shorten the sumifs like =SUMIFS(Transactions!$F:$F, Transactions!$C:$C, "="&A7, DATES "<="&$D$2) - this does not work

On a related note, do you think, the use of such things as DATES helps decrease the size of the file?

[AVM]

FOTIS1991: in rows 1-4 I have some headings

[AVM]

ROMPERSTOMPER: pardon for not full info provided: LastRow was defined as =COUNTA(Transactions!$A:$A)+3

[romperstomper]

ROMPERSTOMPER: Am I correct in understanding you that the fact that the formula refer to a million of cells does not mean that excel has to store the empty cells?

Correct. (I assumed that the chances of the first 4 rows meeting the criteria would be marginal). SUMIFS is optimised to work well with entire column references.

If you are going to use dynamic ranges, try to avoid OFFSET as it's a volatile function and will recalculate whenever the workbook does. And make sure that all the ranges are the same size - in your example, your DATES range is not a full column, which is why the formula doesn't work - the other 2 ranges are full columns.

[AVM]

ROMPERSTOMER: Would the use of $A:$A rather than $F$5:$F$1048576 help me decrease the file size or this is just a matter of good "style"? I would hate making many changes in my file and end up with the same size. Basically, this key issue I am now trying to address eventually is the file size - I am trying to see why it becomes bulky and judging my your statement "the formula refer to a million of cells does not mean that excel has to store the empty cells" I gather that the sumifs do not seem to create the issue

[AVM]

ROMPERSTOMER: maybe, the use of $F$5:$F$1048576 rather than $A:$A slows down the calculations, you mean?

[romperstomper]

You would have a slightly smaller file purely because the formula string is shorter. I doubt it would be material unless you have millions of them.

[AVM]

Thank you everyone for this dissuasion. This is very helpful. So how could I replace the arguments in SUMIFS function with definitions like DATES = $A:$A ? The use of sumifs like this SUMIFS(Transactions!$F:$F, Transactions!$C:$C, "="&A7, DATES "<="&$D$2) with defining DATES as $A:$A does not work.

[vikaskamble87]

First of all filter the whole sheet and then delete the empty rows. This might help u..

[AVM]

VIKASKAMMBLE87: I did try deleting all empty rows. To no avail. Did not get the first part you suggested

[romperstomper]

We always need a better description than "doesn't work".

You need to define DATES as Transactions!$A:$A and then use
SUMIFS(Transactions!$F:$F, Transactions!$C:$C, "="&A7, DATES,"<="&$D$2)

[AVM]

ROMPERSTOMPER. Thanks a lot! This works. I had messed up somewhere

[vikaskamble87]

Dear Sir,
Please send a sample working file. i want to learn this.