Way too many SUMIFS functions slowing down performance

[tchung95]

I'm experiencing really slow calculations times for my spreadsheet due to having tons and tons of SUMIFS functions. I have a dataset like this:

Date and Time	Asset	Shares Held	Total Cost	Cost per Share
03/03/2023 19:01	GRT	105326	£2026.81	£0.86
04/03/2023 14:45	APL	51851	£3105.80	£0.41
04/03/2023 18:28	TNC	18510	£186.51	£0.35

I'm just giving an example so the numbers are just for show. I need to work out the exact amount of shares I have for that asset at that specific point in time and same for the exact cost at that point in time to work out the cost per share at that point in time when I make a sale to work out the profit/loss for that sale. I have several different sources for obtaining the shares so I separate each source out into a separate tab when recording and the formula I use is along the lines of:

=SUM(SUMIFS(Source1!C:C,Source1!A:A,"<"&A2,Source1!B:B,B2)+SUMIFS(Source2!C:C,Source2!A:A,"<"&A2,Source2!B:B,B2)+SUMIFS(Source3!C:C,Source3!A:A,"<"&A2,Source3!B:B,B2))

So I am asking the SUMIFS to sum up all the shares bought with the 2 conditions that it must be before the date time stamp of this row and the asset name must match the asset name of this row and then sum up the total across all 3 sheets.

I then need to run a similar formula for cost

But this is really really slow. It was fine at first with a few hundred rows of data but now at a few thousand it takes 10-15 minutes every time to recalculate.

Not sure if this was clear but does anyone know any way of optimising this formula to make things faster? I'm also constantly adding new rows to it every day

 

[GuzmanServices]

A quick tip, insert a blank row at the end of your data and write all SUMIFS from the beginning row to this last blank line. Any additional data needs to be inserted to maintain the SUMIFS integrity.

 

[Joe4]

Welcome to the Board!

As Guzman Services was alluding too, using entire column references can slow the code down like ("B:B").
If you can change those to definite ranges (like "B2:B5000"), it should help.
I sometimes just choose a row number I know is safe and I will never hit (but not too much higher than my last possible row number).

Another comment. It appears to me what you may actually have is a Relational Database model (lots of inter-related data).
If so, a Relational Database program, like Microsoft Access or SQL, will typically handle this kind of stuff more more efficiently and gracefully (provided the database is designed well).

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: SUMIFS function slowing down performance
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[RoryA]

The SUM part of the formula is actually unnecessary here but probably not impacting things. I would suggest you look at using Power Query to aggregate the data from all the sheets into one data set, then build a pivot table off it.