SUMIF slowing calculation times

OvernightCellebrity

New Member
Joined
Jan 16, 2024
Messages
16
Office Version
  1. 365
Platform
  1. Windows
I have the following formula and data (please see images below). How can I improve or modify the formula to avoid slowing down the excel workbook? Note that the sheet contains around 25 tabs an the formula is used multipe times within each tab with various criteria.

I would like to avoid Pivot tables
I cannot change the structure of the underlying data
I need the formula to look for every single data point in 'List of funds' see image.

The goal of the formula is to sum/pull/add all values together based on 2 distinct criteria - FUND NAME and Amount_EUR

The problem is the FUND name which has 25 distinct values.

=SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$I$5)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$K$10)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$I$7)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$I$8)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$I$9)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$I$6)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$I$10)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$I$11)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$I$12)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$I$13)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$J$5)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$J$6)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$J$7)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$J$8)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$J$9)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$J$10)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$J$11)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$J$12)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$J$13)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$K$5)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$K$6)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$K$7)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$K$8)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$K$9)+SUMIFS(INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),DailyCash!$A$5:$A$70000,B5,DailyCash!$C$5:$C$70000,$K$11)
 

Attachments

  • Capture 2 sheet.PNG
    Capture 2 sheet.PNG
    20.6 KB · Views: 11
  • Capture 1 data.PNG
    Capture 1 data.PNG
    63.7 KB · Views: 12

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Since you have 365, you could use something like this:

Excel Formula:
=LET(
dataCol,INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),
crit1,DailyCash!$A$5:$A$70000,
crit2,DailyCash!$C$5:$C$70000,
crit2value,TOCOL($I$5:$K$11,3),
SUM(FILTER(dataCol,MAP(crit1,crit2,LAMBDA(a,b,AND(a=B5,OR(b=crit2value))))))
)
 
Upvote 1
Solution
Thank you for your response. Please find the revised screenshots attached. I Have added all the data in a single screenshot if easier to visualise the problem ( see

The data I need to extract is in Column `DailyCash!$I$5:$I$70000` (highlighted in red).

The extraction is based on the following criteria:

1. The value in cell `B5` matches the values in Column `DailyCash!$A$5:$A$70000` (highlighted in blue).
2. The values in cells `I5:K25` (highlighted in green) matches the values in Column `DailyCash!$C$5:$C$70000` (highlighted in yellow).

Would the appropriate formula then be?

=LET(DailyCash!$I$5:$I$70000,INDEX(DailyCash!$G$5:$I$70000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),
I5,DailyCash!$A$5:$A$70000,
I6,DailyCash!$C$5:$C$70000,
I7,TOCOL($I$5:$K$11,3),
SUM(FILTER(dataCol,MAP(I5,I6,LAMBDA(a,b,AND(a=B5,OR(b=crit2value))))))
)

Thank you in advance for the help
 

Attachments

  • Capture 1 data.PNG
    Capture 1 data.PNG
    68.2 KB · Views: 9
  • Capture 2 sheet.PNG
    Capture 2 sheet.PNG
    31.2 KB · Views: 7
  • All Data.PNG
    All Data.PNG
    61.5 KB · Views: 8
Upvote 0
No, the formula is exactly what I posted. That is the equivalent of your original formula.
 
Upvote 0
Try this formula.
Excel Formula:
=LET(a,INDEX(DailyCash!$G$5:$I$17000,,MATCH($F$4,DailyCash!$G$4:$I$4,0)),ro,IF((DailyCash!A5:A17000=B5)*(COUNTIF(I5:K13,DailyCash!C5:C17000)>0),a,""),SUM(ro))
 
Upvote 0
One Further question: If i needed to adjust the formula to include additional critical criteria e.g. the date in cell C16 as Crit 3.

AND for the formula to change the source data instead look at 'Pipeline' data sheet - how would i amend the below so that it doesnt return a CALC error 'EMPTY ARRAY'?

Assume it returns this becuase there is no value to find?

=LET(
dataCol, INDEX(Pipeline!$C$4:$E$70000, , MATCH($F$5, Pipeline!$C$4:$E$4, 0)),
crit1, Pipeline!$B$4:$B$70000,
crit2, Pipeline!$C$4:$C$70000,
crit2value, TOCOL($N$5:$P$13, 3),
crit3, Pipeline!$D$4:$D$70000,
SUM(FILTER(dataCol, MAP(crit1, crit2, crit3, LAMBDA(a,b,c, AND(a = B20, OR(b = crit2value), c = C16)))))
)
 

Attachments

  • Excel LET Data.PNG
    Excel LET Data.PNG
    10.5 KB · Views: 8
  • Excel LET sheet.PNG
    Excel LET sheet.PNG
    49 KB · Views: 9
Upvote 0
You can return 0 if there is no matching data:

Excel Formula:
=LET(
dataCol, INDEX(Pipeline!$C$4:$E$70000, , MATCH($F$5, Pipeline!$C$4:$E$4, 0)),
crit1, Pipeline!$B$4:$B$70000,
crit2, Pipeline!$C$4:$C$70000,
crit2value, TOCOL($N$5:$P$13, 3),
crit3, Pipeline!$D$4:$D$70000,
SUM(FILTER(dataCol, MAP(crit1, crit2, crit3, LAMBDA(a,b,c, AND(a = B20, OR(b = crit2value), c = C16))),0))
)
 
Upvote 0
Pl try formula in Post#6.
To add criteria Give the date range in DailyCash sheet.
I like the solution in Post 6# but when i apply it to the data in post 7 it returns an #N/A

See formula below

Excel Formula:
=LET(a,INDEX(Pipeline!$C$4:$E$70000,,MATCH($C$16,Pipeline!$C$4:$E$70000,0)),ro,IF((Pipeline!B4:B70000=B20)*(COUNTIF(N5:P13,Pipeline!$C$4:$C$70000)>0),a,""),SUM(ro))
 
Upvote 0

Forum statistics

Threads
1,224,568
Messages
6,179,595
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top