Suggestions on how to re-write this formula for faster calculations

keef2

Board Regular
Joined
Jun 30, 2022
Messages
185
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have this formula that is in the below table D4:O15 that takes quite a longtime to calculate. I am curious if anyone has other options or ways to calculate faster. I also ran some analysis to understand time constraints and other metrics. Any help in revising this formula to make things work faster would be greatly appreciated.

working Sales Table VBA - No Metrics backup.xlsm
BCDEFGHIJKLMNOPQ
1Monthly Sales
3 January Febuary March April May June July August September October November December TOTAL SALES
42012$ 217,787$ 714,700$ 80,019$ 7,908,552$ 3,674,515$ 522,802$ 2,398,672$ 1,251,678$ 28,843$ 17,573$ 9,400$ 847,565$ 17,672,1062012
52013$ 3,093,415$ 1,090,780$ 1,623,840$ 1,796,440$ 1,717,774$ 1,044,612$ 1,825,158$ 3,733,815$ 637,388$ 869,792$ 2,370,985$ 823,075$ 20,627,0742013
62014$ 2,078,895$ 2,626,000$ 6,653,426$ 36,380$ 2,859,745$ 831,390$ 1,937,547$ 3,483,615$ 2,140,838$ 647,872$ 1,137,422$ 67,600$ 24,500,7302014
72015$ 2,533,585$ 2,091,747$ 4,494,199$ 1,702,275$ 1,005,674$ 2,167,017$ 7,680,620$ 110,592$ 3,045,928$ 10,878,519$ 95,300$ 3,564,216$ 39,369,6722015
82016$ 409,465$ 1,832,825$ 3,244,407$ 3,904,514$ 2,306,938$ 5,429,274$ 843,556$ 8,298,244$ 4,035,878$ 1,192,207$ 7,591,000$ 92,750$ 39,181,0582016
92017$ 3,465,280$ 1,513,817$ 6,584,535$ 879,800$ 1,218,100$ 2,192,697$ 298,450$ 3,302,787$ 2,806,055$ 3,908,009$ 458,937$ 1,412,888$ 28,041,3552017
102018$ 4,351,558$ 270,195$ 2,116,150$ 5,489,089$ 2,985,495$ 2,456,650$ 8,331,072$ 2,442,865$ 3,261,885$ 367,150$ 304,861$ 3,049,779$ 35,426,7492018
112019$ 113,838$ 7,071,370$ 1,380,520$ 747,983$ 5,226,733$ 1,606,520$ 7,148,845$ 1,351,090$ 792,577$ 7,278,820$ 4,818,686$ 2,516,597$ 40,053,5792019
122020$ 1,057,457$ 5,948,478$ 2,712,030$ 1,014,750$ 1,511,408$ 1,182,030$ 4,516,640$ 3,857,360$ 2,769,944$ 1,685,050$ 1,704,698$ 2,772,694$ 30,732,5392020
132021$ 4,090,350$ 2,652,478$ 9,143,516$ 9,014,203$ 6,060,405$ 2,350$ 8,571,295$ 11,607,380$ 535,470$ 13,199,409$ 4,606,389$ 383,525$ 69,866,7702021
142022$ 8,128,763$ 5,401,750$ 3,765,450$ 12,984,905$ 7,812,055$ 319,975$ 500,800$ 97,500 $ 39,011,1982022
16
17AVERAGE$ 2,685,490$ 2,837,649$ 3,799,827$ 4,134,445$ 3,307,167$ 1,614,120$ 4,004,787$ 3,594,266$ 2,005,481$ 4,004,440$ 2,309,768$ 1,553,069$ 34,952,985AVERAGE
18
196Yr AVG$ 3,534,541$ 3,809,681$ 4,283,700$ 5,021,788$ 4,135,699$ 1,293,370$ 4,894,517$ 3,776,497$ 2,366,968$ 4,605,108$ 3,247,429$ 1,704,706$ 40,522,0326 Yr AVG
202020Vs 6 Yr AVG$ (2,477,084)$ 2,138,797$ (1,571,670)$ (4,007,038)$ (2,624,291)$ (111,340)$ (377,877)$ 80,863$ 402,976$ (2,920,058)$ (1,542,731)$ 1,067,989$ (9,789,493)Vs 2020
21
22Compare Years
2320212020$ 3,032,893$(3,296,000)$ 6,431,486$ 7,999,453$ 4,548,997$ (1,179,680)$ 4,054,655$ 7,750,020$ (2,234,474)$ 11,514,359$ 2,901,691$ (2,389,169)$ 39,134,231Compare Yrs
SALES SUMMARY
Cell Formulas
RangeFormula
D4:O14D4=IF(SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='SALES SUMMARY'!D$2)*(YEAR(MASTER!$E:$E)='SALES SUMMARY'!$C4),MASTER!$G:$G)))=0,"",(SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='SALES SUMMARY'!D$2)*(YEAR(MASTER!$E:$E)='SALES SUMMARY'!$C4),MASTER!$G:$G)))))
P4:P14P4=IF(SUM(D4:O4)=0,"",SUM(D4:O4))
Q4:Q14Q4=C4
C5:C14C5=C4+1
D17:P17D17=AVERAGE(D4:D15)
Q17Q17=+C17
D19:P19D19=AVERAGE(OFFSET(D3,COUNT(D4:D15),0,-$B$19))
Q19Q19=CONCATENATE(B19, " ",C19)
C20C20=CONCATENATE("Vs"," ",B19," ",C19)
D20D20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,2,FALSE)-D19,"")
E20E20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,3,FALSE)-E19,"")
F20F20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,4,FALSE)-F19,"")
G20G20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,5,FALSE)-G19,"")
H20H20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,6,FALSE)-H19,"")
I20I20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,7,FALSE)-I19,"")
J20J20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,8,FALSE)-J19,"")
K20K20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,9,FALSE)-K19,"")
L20L20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,10,FALSE)-L19,"")
M20M20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,11,FALSE)-M19,"")
N20N20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,12,FALSE)-N19,"")
O20O20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,13,FALSE)-O19,"")
P20P20=IFERROR(VLOOKUP($B$20,$C$4:$P$15,14,FALSE)-P19,"")
Q20Q20=CONCATENATE("Vs ",B20)
D23D23=VLOOKUP($B$23,$C$4:$P$15,2,FALSE)-VLOOKUP($C$23,$C$4:$P$15,2,FALSE)
E23E23=VLOOKUP($B$23,$C$4:$P$15,3,FALSE)-VLOOKUP($C$23,$C$4:$P$15,3,FALSE)
F23F23=VLOOKUP($B$23,$C$4:$P$15,4,FALSE)-VLOOKUP($C$23,$C$4:$P$15,4,FALSE)
G23G23=VLOOKUP($B$23,$C$4:$P$15,5,FALSE)-VLOOKUP($C$23,$C$4:$P$15,5,FALSE)
H23H23=VLOOKUP($B$23,$C$4:$P$15,6,FALSE)-VLOOKUP($C$23,$C$4:$P$15,6,FALSE)
I23I23=VLOOKUP($B$23,$C$4:$P$15,7,FALSE)-VLOOKUP($C$23,$C$4:$P$15,7,FALSE)
J23J23=VLOOKUP($B$23,$C$4:$P$15,8,FALSE)-VLOOKUP($C$23,$C$4:$P$15,8,FALSE)
K23K23=VLOOKUP($B$23,$C$4:$P$15,9,FALSE)-VLOOKUP($C$23,$C$4:$P$15,9,FALSE)
L23L23=VLOOKUP($B$23,$C$4:$P$15,10,FALSE)-VLOOKUP($C$23,$C$4:$P$15,10,FALSE)
M23M23=VLOOKUP($B$23,$C$4:$P$15,11,FALSE)-VLOOKUP($C$23,$C$4:$P$15,11,FALSE)
N23N23=VLOOKUP($B$23,$C$4:$P$15,12,FALSE)-VLOOKUP($C$23,$C$4:$P$15,12,FALSE)
O23O23=VLOOKUP($B$23,$C$4:$P$15,13,FALSE)-VLOOKUP($C$23,$C$4:$P$15,13,FALSE)
P23P23=VLOOKUP($B$23,$C$4:$P$15,14,FALSE)-VLOOKUP($C$23,$C$4:$P$15,14,FALSE)



Workbook Metrics for time.xlsx
ABCDEFGH
3Physical EnvironmentProfile:8/23/2022 13:46\\192.168.1.2\userhomedir$\kmiles\Desktop\working Sales Table VBA - No Metrics backup.xlsm
4RAM(MB)4,19515,293Workbook Settings
5Swap File(MB)6,78317,597SharedDecimal PlacesProtection
6Excel Memory(MB)2,059131,072SharedFALSEFixedFALSEStructure
7FileSize(K)918ChangesTRUEPlaces2Worksheets
8Operating SystemWindows 10 .19042 x64SavedFALSEBackupTRUEPassword
9MHZ x Cores3194 x 8FormatXMLWorkbookMacroStyles50Views
10 Excel VersionExcel 365.15427 x64Environment Counts XLB/Qat (K)Com Addins
11XL Calc Engine191029 Dyn ArrayTemp75VBE9 Excel Addins
12
13Single-Threaded WorkSheet Formulas Profile for SALES SUMMARY
14FormulaFormulaMicrosecsTotal MillisecsFormula %
15AddressFlags Unique Formulas Count/FormulaThis FormulaArea/Sheetof Sheet Time
16$D$4:$O$15B-N-M=IF(SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='SALES SUMMARY'!D$2)*(YEAR(MASTER!$E:$E)='SALES SUMMARY'!$C4),MASTER!$G:$G)))=0,"",(SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='SALES SUMMARY'!D$2)*(YEAR(MASTER!$E:$E)='SALES SUMMARY'!$C4),MASTER!$G:$G)))))1441,900,979.58273,741.06273,794.12100%
17$K$28:$K$39B-N-M=SUMIFS('SALES BY PM'!$O:$O,'SALES BY PM'!$A:$A,H28)12351.784.22273,794.120%
18$Q$28:$Q$39B-N-M=SUMIFS('SALES BY PM'!$W:$W,'SALES BY PM'!$A:$A,H28)12349.164.19273,794.120%
19$N$28:$N$39B-N-M=SUMIFS('SALES BY PM'!$T:$T,'SALES BY PM'!$A:$A,H28)12347.744.17273,794.120%
20$M$28:$M$39B-N-M=SUMIFS('SALES BY PM'!$S:$S,'SALES BY PM'!$A:$A,H28)12343.954.13273,794.120%
21$I$28:$I$39B-N-M=SUMIFS('SALES BY PM'!$L:$L,'SALES BY PM'!$A:$A,'SALES SUMMARY'!H28)12341.094.09273,794.120%
22$L$28:$L$39B-N-M=SUMIFS('SALES BY PM'!$R:$R,'SALES BY PM'!$A:$A,H28)12340.014.08273,794.120%
FastXLFuncs1



Workbook Metrics for time.xlsx
ABCD
98LocalFunction EnglishFunction
99Function NameFlags Function NameCount
100IFB-N-MIF744
101SUMB-N-MSUM344
102ISNUMBERB-N-MISNUMBER288
103MONTHB-N-MMONTH288
104YEARB-N-MYEAR288
105ROWB-N-MROW3
106ROWSB-N-MROWS1
107AVERAGEB-N-MAVERAGE28
108OFFSETB-V-MOFFSET13
109COUNTB-N-MCOUNT13
110CONCATENATEB-N-MCONCATENATE3
111IFERRORB-N-MIFERROR36
112VLOOKUPB-N-MVLOOKUP87
113SUMIFSB-N-MSUMIFS108
FastXLFuncs1
 
As you have 365 you could use let & filter like
Excel Formula:
=LET(f,SUM(FILTER(Master!$G:$G,(MONTH(Master!$E:$E)=D$2)*(YEAR(Master!$E:$E)=$C4),"")),IF(f="","",f))
working Sales Table VBA - Test.xlsm
D
4#VALUE!
SALES SUMMARY
Cell Formulas
RangeFormula
D4D4=LET(f,SUM(FILTER(MASTER!$G:$G,(MONTH(MASTER!$E:$E)=D$2)*(YEAR(MASTER!$E:$E)=$C4),"")),IF(f="","",f))


Comes up with an error?
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
It should be significantly faster, but it's still best to avoided whole column references (which are never needed).
how to you ensure you capture all data without whole column references? So if a user continues to add data to the master sheet how do you make sure that the range is always capturing all the data?
 
Upvote 0
You could try a Pivot Table.

2 quick examples with formulas
one uses a helper column

Sumifs.xlsm
ABCDE
1HelperDatesAmounts
220120101-01-121000000
320120102-01-122000000
420120104-01-123000000
520120202-02-124000000
6
Master
Cell Formulas
RangeFormula
A2:A5A2=YEAR(D2)&TEXT(MONTH(D2),"00")


Sumifs.xlsm
CDE
3JanuaryFebruary
420126,000,000.004,000,000.00
Sales Summary
Cell Formulas
RangeFormula
D4D4=SUMPRODUCT(Master!$E$2:$E$10,--(Master!$D$2:$D$10-DAY(Master!$D$2:$D$10)+1=DATE(C4,MONTH(1&D3),1)))
E4E4=SUMPRODUCT(Master!$E$2:$E$10,--(Master!A2:A10=C4&TEXT(MONTH(E3&1),"00")))
 
Upvote 0
how to you ensure you capture all data without whole column references?
Just set the range to something larger than you would ever expect to use without going over the top, like
Excel Formula:
=LET(f,SUM(FILTER(Master!$G2:$G10000,(MONTH(Master!$E2:$E10000)=D$2)*(YEAR(Master!$E2:$E10000)=$C4),"")),IF(f="","",f))
You are probably getting the error because you have text values in col E
 
Upvote 0
Solution
Just set the range to something larger than you would ever expect to use without going over the top, like
Excel Formula:
=LET(f,SUM(FILTER(Master!$G2:$G10000,(MONTH(Master!$E2:$E10000)=D$2)*(YEAR(Master!$E2:$E10000)=$C4),"")),IF(f="","",f))
You are probably getting the error because you have text values in col E
Yo Fluff! your a rock star LET function is pretty slick and extremely fast! Thank you so much for your help.
 
Upvote 0
You could try a Pivot Table.

2 quick examples with formulas
one uses a helper column

Sumifs.xlsm
ABCDE
1HelperDatesAmounts
220120101-01-121000000
320120102-01-122000000
420120104-01-123000000
520120202-02-124000000
6
Master
Cell Formulas
RangeFormula
A2:A5A2=YEAR(D2)&TEXT(MONTH(D2),"00")


Sumifs.xlsm
CDE
3JanuaryFebruary
420126,000,000.004,000,000.00
Sales Summary
Cell Formulas
RangeFormula
D4D4=SUMPRODUCT(Master!$E$2:$E$10,--(Master!$D$2:$D$10-DAY(Master!$D$2:$D$10)+1=DATE(C4,MONTH(1&D3),1)))
E4E4=SUMPRODUCT(Master!$E$2:$E$10,--(Master!A2:A10=C4&TEXT(MONTH(E3&1),"00")))
Dave, Thank you for the examples, pivot tables are just something id rather stay away from. These are great alternatives. I will test them out vs the LET function but right now its cruisin' pretty good! Thanks again for the time and suggestions.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

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