# Need to lighten the calculation load ... help?

#### Sven62

##### Active Member
The following code occurs 231 times each on six sheets. That's 1386 calculations for every new entry.

Anyone have a great idea for speeding this up?

Code:
=SUMPRODUCT(--(OVERTIME!\$B\$4:\$B\$2048>=DATA!D\$2),--(OVERTIME!\$B\$4:\$B\$2048<=DATA!D\$3),--(OVERTIME!\$K\$4:\$K\$2048="Monday"),--(OVERTIME!\$D\$4:\$D\$2048=TRUE),--(OVERTIME!\$C\$4:\$C\$2048=1),OVERTIME!\$G\$4:\$G\$2048)
+SUMPRODUCT(--(OVERTIME!\$B\$4:\$B\$2048>=DATA!D\$2),--(OVERTIME!\$B\$4:\$B\$2048<=DATA!D\$3),--(OVERTIME!\$K\$4:\$K\$2048="Monday"),--(OVERTIME!\$D\$4:\$D\$2048=TRUE),--(OVERTIME!\$C\$4:\$C\$2048=1),OVERTIME!\$H\$4:\$H\$2048)
+SUMPRODUCT(--(OVERTIME!\$B\$4:\$B\$2048>=DATA!D\$2),--(OVERTIME!\$B\$4:\$B\$2048<=DATA!D\$3),--(OVERTIME!\$K\$4:\$K\$2048="Monday"),--(OVERTIME!\$D\$4:\$D\$2048=TRUE),--(OVERTIME!\$C\$4:\$C\$2048=1),OVERTIME!\$I\$4:\$I\$2048)

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
yes what excel do you have? IF 2007 or later try
=SUMIFS(OVERTIME!\$G\$4:2048,OVERTIME!\$B\$4:\$B\$2048,"<="&DATA!D\$3,OVERTIME!\$k\$4:\$k\$2048,"MONDAY",OVERTIME!\$D\$4:\$D\$2048,"TRUE",OVERTIME!\$C\$4:\$C\$2048,1)

Last edited:
The following code occurs 231 times each on six sheets. That's 1386 calculations for every new entry.

Anyone have a great idea for speeding this up?

Code:
=SUMPRODUCT(
--(OVERTIME!\$B\$4:\$B\$2048>=DATA!D\$2),
--(OVERTIME!\$B\$4:\$B\$2048<=DATA!D\$3),
--(OVERTIME!\$K\$4:\$K\$2048="Monday"),
--(OVERTIME!\$D\$4:\$D\$2048=TRUE),
--(OVERTIME!\$C\$4:\$C\$2048=1),
OVERTIME!\$G\$4:\$G\$2048)
+
SUMPRODUCT(
--(OVERTIME!\$B\$4:\$B\$2048>=DATA!D\$2),
--(OVERTIME!\$B\$4:\$B\$2048<=DATA!D\$3),
--(OVERTIME!\$K\$4:\$K\$2048="Monday"),
--(OVERTIME!\$D\$4:\$D\$2048=TRUE),
--(OVERTIME!\$C\$4:\$C\$2048=1),
OVERTIME!\$H\$4:\$H\$2048)
+
SUMPRODUCT(
--(OVERTIME!\$B\$4:\$B\$2048>=DATA!D\$2),
--(OVERTIME!\$B\$4:\$B\$2048<=DATA!D\$3),
--(OVERTIME!\$K\$4:\$K\$2048="Monday"),
--(OVERTIME!\$D\$4:\$D\$2048=TRUE),
--(OVERTIME!\$C\$4:\$C\$2048=1),
OVERTIME!\$I\$4:\$I\$2048)

Try to switch a more appriate formula:
Control+shift+enter, not just enter:
Rich (BB code):
=SUM(
IF(OVERTIME!\$B\$4:\$B\$2048>=DATA!D\$2,
IF(OVERTIME!\$B\$4:\$B\$2048<=DATA!D\$3,
IF(OVERTIME!\$K\$4:\$K\$2048="Monday",
IF(OVERTIME!\$D\$4:\$D\$2048,
IF(OVERTIME!\$C\$4:\$C\$2048=1,
OVERTIME!\$G\$4:\$I\$2048))))))

If still short on performance, let's concatenate:

Overtime, L4, just enter, and copy down:
Rich (BB code):
=C4&"|"&D4+0&"|"&K4

Now invoke, control+shift+enter, not just enter:
Rich (BB code):
=SUM(
IF(OVERTIME!\$B\$4:\$B\$2048>=DATA!D\$2,
IF(OVERTIME!\$B\$4:\$B\$2048<=DATA!D\$3,
IF(OVERTIME!\$L\$4:\$L\$2048=1&"|"&1&"Monday"
OVERTIME!\$G\$4:\$I\$2048))))

Message deleted

This worked great.

This worked great.

Good to hear that. Thanks for providing feedback.

BTW how do I format my code on separate lines as you have done? Carriage return does not work!

BTW how do I format my code on separate lines as you have done? Carriage return does not work!

Try using the code tag. That is, [ followed by code followed by ] formula [ followed by / followed by code followed by ].

I mean in Excel

Replies
3
Views
196
Replies
0
Views
126
Replies
4
Views
169
Replies
3
Views
108
Replies
3
Views
194

1,196,057
Messages
6,013,163
Members
441,751
Latest member
336448

### 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.

### Which adblocker are you using?

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

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