Need to lighten the calculation load ... help?

[Sven62]

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?

=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)

 

[jamtay317]

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)

 

[Aladin Akyurek]

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?

=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:

=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:

=C4&"|"&D4+0&"|"&K4

Now invoke, control+shift+enter, not just enter:

=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))))

 

[Sven62]

Message deleted

 

[Sven62]

This worked great.

 

[Aladin Akyurek]

This worked great.

Good to hear that. Thanks for providing feedback.

 

[Sven62]

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

 

[Aladin Akyurek]

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

 

[Sven62]

I mean in Excel

 

[Aladin Akyurek]

I mean in Excel

If you mean on the Formula Bar, try Alt + Enter.