SUMPRODUCT suggestion to speed up

I have a data field from D1 to ND1 as 16-02-2018 to 15-02-2019 (365 days). I have data from D2 to ND611. I have used SUMPRODUCT formula in two sheets TOTAL and MONTH_WISE which helps to get the output data total and also month-wise based on FROM and TO dates setup in TOTAL sheet.
The formula is correct and I have verified it. But, it is slow when I am entering the data. I understand that since SUMPRODUCT works array logic, it consumes time. Any suggestions on improving the speed?

The work file is attached herewith

Thanks, I appreciate the brevity introduced in the formula. There is no appreciable improvement in speed. I know “speed” is a relative term. Eager to know if we can use SUMIFS for this purpose.

Looking at your sample file in more detail, I just noticed that the dates in row 5 of your month_wise sheet are not proper dates, so a little more manipulation is required there.

In E6, then drag / fill the table to Q615

=SUMIFS(INDEX(data!$D$2:$ND$650,MATCH([@[Part Flag]],data!$A$2:$A$650,0),0),data!$D$1:$ND$1,">="&DATEVALUE(1&E$5),data!$D$1:$ND$1,"<="&EOMONTH(DATEVALUE(1&E$5),0))

Also, given that the TOTAL sheet appears to be just that, the dates there are moot. You just need

=SUM(INDEX(data!$D$2:$ND$650,MATCH([@[Part Flag]],data!$A$2:$A$650,0),0))

Or, even simpler, because it is already calculated in the month_wise sheet, just get the existing total from there with

=INDEX(MNT[Qty Total],MATCH([@[Part Flag]],MNT[Part Flag],0))

Using your method, the formula in E6 should be

=SUMIFS(INDEX(data!$D$2:$ND$650,MATCH($B6,data!$A$2:$A$650,0),0),data!$D$1:$ND$1,">="&$C$1,data!$D$1:$ND$1,">="&DATEVALUE(1&E$5),data!$D$1:$ND$1,"<="&$C$2,data!$D$1:$ND$1,"<="&EOMONTH(DATEVALUE(1&E$5),0))

The extra EOMONTH function, along with <= instead of >= was comparing wrong dates.
INT() was not needed, you only need to use that to sepatate date from time when you have both together.

This method might be slightly faster to calculate, not the use of MAX and MIN to determine the correct start and end dates to reduce the number of criteria in sumifs.

=SUMIFS(INDEX(data!$D$2:$ND$650,MATCH($B6,data!$A$2:$A$650,0),0),data!$D$1:$ND$1,">="&MAX($C$1,DATEVALUE(1&E$5)),data!$D$1:$ND$1,"<="&MIN($C$2,EOMONTH(DATEVALUE(1&E$5),0)))

Faster still, would be to enter this formula into A6 and fill down

=MATCH(MNT_4[@[Part Flag]],data!$A$2:$A$611,0)

Then use this in E6 to fill the table

=SUMIFS(INDEX(data!$D$2:$ND$650,$A6,0),data!$D$1:$ND$1,">="&MAX($C$1,DATEVALUE(1&E$5)),data!$D$1:$ND$1,"<="&MIN($C$2,EOMONTH(DATEVALUE(1&E$5),0)))

Which will significantly reduce the number of times an exact match needs to be calulated, which by now is one of the biggest drains on calculation resources.

Note that with any one of these formulas in the month_wise sheet, the figure in the TOTAL sheet will be the total from the custom dates entered in month_wise, not the total of the whole sheet.

To get the total of the whole sheet regardless of the custom dates, you would need the SUM(INDEX formula from my previous post.

Originally Posted by jason.b75

Using your method, the formula in E6 should be

=SUMIFS(INDEX....

Jason,
I tried both options and works as desired. Perhaps, I messed up with the “<=” & “>=” sign with dates with the use of INT, which was not the right thing to use.
I understood how referring the ‘Partflag’ field helps reduce iterations.
The use of INDEX-MATCH with SUMIFS was incisive. I also set the calculation as MANUAL instead of the default automatic.
This method is way better than SUMPRODUCT which consumes time. SUMPRODUCT is so convenient that we tend to use it often and not mindful of the fact that it consumes time.
Thanks for the wonderful explanation.

Why set calculation to manual, when you can speed it up even more? This method eliminates sumifs calculation where it is not needed, i.e. months outside of the range specified in C1 and C2. Making it almost instant if the dates only cover a short period (If the 'To Date' in C2 is empty, no sumifs calcs are performed, so a quick way to speed up data entry)

It also includes efficient elimination of the #N/A errors that you will have in various places.

A list of the formulas used, already entered in the attached copy of your file.

month_wise sheet
E1, fill right to Q1

Formula:

Please Login or Register  to view this content.

E2, then fill right to Q2

Formula:

Please Login or Register  to view this content.

A6, down to A615

Formula:

Please Login or Register  to view this content.

C6, right to D6, then down to C615 & D615

Formula:

Please Login or Register  to view this content.

E6, right and down to Q615

Formula:

Please Login or Register  to view this content.

D6, down to R615

Formula:

Please Login or Register  to view this content.

S6, down to S615

Formula:

Please Login or Register  to view this content.

TOTAL sheet, as this is a direct copy of select columns of the month_wise sheet, all index / match functions can be removed here and replace with a very simple formula.
B3, right and down to D636

Formula:

Please Login or Register  to view this content.

E3, right and down to F636

Formula:

Please Login or Register  to view this content.

I think that it could possibly be further improved by using MATCH to get column numbers instead of dates in row 1 of month_wise, meaning that you could then use SUM(INDEX( which would be more efficient than SUMIFS, but I can't get the logic to function correctly.

Originally Posted by jason.b75

Why set calculation to manual, when you can speed it up even more? ....

Thanks for taking time to understand my requirement and simplifying the problem even further. With all suggestions into account, the sheet works charm. Included a few custom requirements with VBA also
Thanks a lot once again.

Originally Posted by avk

As per my view no need of total sheet. Because in monthwise sheet already calculate total qty & amount.

How would you go about printing the data on the Total sheet from the month_wise sheet?

Which is what I did, the TOTAL worksheet was already there in the sample provided (See post #1). All I did was speed it up, nothing added , nothing taken away.

If the question about part flag and Sl no was directed at me, then again, this was part of the original, I don't think that I changed it, the existing formula was simple and efficient.