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
I have modified formula. Pl try.
Excel Formula:
=LET(a,INDEX(Pipeline!$C$4:$E$70000,,MATCH($C$16,Pipeline!$C$4:$E$4,0)),ro,IF((Pipeline!B4:B70000=B20)*(COUNTIF(N5:P13,Pipeline!$C$4:$C$70000)>0),a,""),SUM(ro))
For match function One Column or one Row should be selected.
 
Upvote 1

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
I have modified formula. Pl try.
Excel Formula:
=LET(a,INDEX(Pipeline!$C$4:$E$70000,,MATCH($C$16,Pipeline!$C$4:$E$4,0)),ro,IF((Pipeline!B4:B70000=B20)*(COUNTIF(N5:P13,Pipeline!$C$4:$C$70000)>0),a,""),SUM(ro))
For match function One Column or one Row should be selected.
Thank you for this i edited it slight to make it work, see below:

Excel Formula:
=LET(a,INDEX(Pipeline!$C$4:$E$70000,,MATCH($F$5,Pipeline!$C$4:$E$4,0)),ro,IF((Pipeline!$B$4:$B$70000=$B20)*(Pipeline!$D$4:$D$70000=C$16)*(COUNTIF($N$5:$P$13,Pipeline!$C$4:$C$70000)>0),a,""),SUM(ro))
 
Upvote 0

Forum statistics

Threads
1,224,567
Messages
6,179,568
Members
452,926
Latest member
rows and columns

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