Hi All
I have a large data sheet currently hitting 70000 rows and growing.
I am currently running a SUMPRODUCT formula to meet my needs, but this grinding the calculation to halt. currently 30 minutes to calculate.
An example if the formulas i am running is:
=SUMPRODUCT(--(Schedule!$A$2:$A$80000>=C2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="man"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=D2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="man"),Schedule!$E$2:$E$80000)+SUMPRODUCT(--(Schedule!$A$2:$A$80000>=C2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$D$2:$D$80000="man"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=D2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$D$2:$D$80000="man"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=C2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="LGW"),Schedule!$E$2:$E$80000)+SUMPRODUCT(--(Schedule!$A$2:$A$80000>=D2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="LGW"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=C2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="LTN"),Schedule!$E$2:$E$80000)+SUMPRODUCT(--(Schedule!$A$2:$A$80000>=D2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="LTN"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=C2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="BHX"),Schedule!$E$2:$E$80000)+SUMPRODUCT(--(Schedule!$A$2:$A$80000>=D2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="BHX"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=C2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="EMA"),Schedule!$E$2:$E$80000)+SUMPRODUCT(--(Schedule!$A$2:$A$80000>=D2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="EMA"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=C2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="LBA"),Schedule!$E$2:$E$80000)+SUMPRODUCT(--(Schedule!$A$2:$A$80000>=D2),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="MAN"),--(Schedule!$D$2:$D$80000="LBA"),Schedule!$E$2:$E$80000)
The main problem is speed.
I had originally set the formulas to run by column range e.g. A:A, but was advised to to specify a range. I set the range to 80000 and this did work in terms of improving calculation, but still frustrates me.
I want to know if there is a way to control the range that the formula looks for instead of it looking at all 80000 row's.
so simplifying the above, the core calulation is:
=SUMPRODUCT(--(Schedule!$A$2:$A$80000>=A1),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="man"),Schedule!$E$2:$E$80000)-SUMPRODUCT(--(Schedule!$A$2:$A$80000>=B1),--(Schedule!$B$2:$B$80000="XXX"),--(Schedule!$C$2:$C$80000="man"),Schedule!$E$2:$E$80000)
A1 = 01 Jan 13
B1 = 08 Jan 13
I am asking the formula to return a weeks worth of data, to do this i am summing all criteria that is greater/equal that 01 Jan 13
then subtracting all criteria that is greater/equal that 08 Jan 13
I essentially double count.
I was thinking that if i could dynacmically set a >between< date range then the performance would improve?
open to suggestions, would SUMIFS or another formula be any faster?
many thanks
Bookmarks