# Converting a slow SUMPRODUCT formula to SUMIFS (or other)

1. ## Converting a slow SUMPRODUCT formula to SUMIFS (or other)

I have a SUMPRODUCT formula which looks at three columns - ORDER, START DATE, HOURS OF WORK to determine negative values in the HOURS OF WORK columns so that I can delete the negative value along with the corresponding positive value.

This worksheet can potentially have hundreds of thousands of rows so as you can image, the SUMPRODUCT solution takes an absolute age, to the point where it's actually quicker to do the job manually. From what I can find online, I believe a SUMIFS formula (even though it may require several extra columns) will complete the calculation much quicker.

Here's what I have at the moment - works perfectly but is extremely slow:

=IF((SUMPRODUCT((\$A\$2:\$A\$1000=A2)*(\$B\$2:\$B\$1000=B2)*(\$C\$2:\$C\$1000=-C2))>0)*(SUMPRODUCT((\$A\$2:\$A2=A2)*(\$B\$2:\$B2=B2)*(\$C\$2:\$C2=C2))=1),"DELETE","")

I've been trying to perform the same calculation with SUMIFS however I just can't get my head around it.

If someone could help me out that would be amazing.

Thanks.  Register To Reply

2. ## Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

any possibility you can upload a small sample with the results included so people can work with the data?  Register To Reply

3. ## Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

Using SUMPRODUCT in this way looks that you need COUNTIFS function and not SUMIFS.  Register To Reply

4. ## Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other) Originally Posted by Sambo kid any possibility you can upload a small sample with the results included so people can work with the data?
Yes, certainly. Thanks.

SUMPRODUCT.xlsx  Register To Reply

5. ## Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other) Originally Posted by Fotis1991 Using SUMPRODUCT in this way looks that you need COUNTIFS function and not SUMIFS.
I gather what I need the formula(s) to be doing is comparing the positive and negative values which have a common date and order number, I'm afraid I just can't get my head around how to do that.   Register To Reply

6. ## Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

Hi.

I don't understand the need for the initial clause in your IF statement, i.e.:

SUMPRODUCT((\$A\$2:\$A\$1000=A2)*(\$B\$2:\$B\$1000=B2)*(\$C\$2:\$C\$1000=C2))>0

Surely this will always be TRUE, since there will always be at least one row which satisfies this condition, namely that in which the formula lies.

Hence you can simply remove that check and use:

=IF(SUMPRODUCT((\$A\$2:\$A2=A2)*(\$B\$2:\$B2=B2)*(\$C\$2:\$C2=C2))=1,"DELETE","")

which is, even better:

=IF(COUNTIFS(\$A\$2:\$A2,A2,\$B\$2:\$B2,B2,\$C\$2:\$C2,C2)=1,"DELETE","")

Regards  Register To Reply

7. ## Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other) Originally Posted by XOR LX I don't understand the need for the initial clause in your IF statement, i.e.:

SUMPRODUCT((\$A\$2:\$A\$1000=A2)*(\$B\$2:\$B\$1000=B2)*(\$C\$2:\$C\$1000=C2))>0
Thanks for the reply, I gather the initial part of the formula is crucial as it contains (\$C\$2:\$C\$1000=-C2) - minus C2

If I only do =IF(COUNTIFS(\$A\$2:\$A2,A2,\$B\$2:\$B2,B2,\$C\$2:\$C2,C2)=1,"DELETE","") then it just gives me duplicate positive values for each start date and order.  Register To Reply

8. ## Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

in your example I think I understand why you are marking rows 9 and 10 for delete but I'm not sure why you have row 11 marked for delete.  Register To Reply

9. ## Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other) Originally Posted by Sambo kid in your example I think I understand why you are marking rows 9 and 10 for delete but I'm not sure why you have row 11 marked for delete.
Row 11 would correspond to Row 16 - matching negative value on the same date and order number.

Thanks.  Register To Reply

10. ## Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other) Originally Posted by scottk82 Thanks for the reply, I gather the initial part of the formula is crucial as it contains (\$C\$2:\$C\$1000=-C2) - minus C2
Ah, apologies. I missed that small detail.

Regards  Register To Reply

11. ## Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

The equivalent would be:

=IF(COUNTIFS(\$A:\$A,A2,\$B:\$B,B2,\$C:\$C,-C2),IF(COUNTIFS(\$A\$2:\$A2,A2,\$B\$2:\$B2,B2,\$C\$2:\$C2,C2)=1,"DELETE",""),"")

in which at least you can reference entire columns with no detriment to performance.

I cannot guarantee that, over very large ranges, this set-up will not also suffer.

Regards  Register To Reply

12. ## Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

I was working out one with additional columns but it looks like XOR LX got it for you so, good luck.   Register To Reply

13. ## Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other) Originally Posted by Sambo kid I was working out one with additional columns but it looks like XOR LX got it for you so, good luck. May be worth posting, though, as a multi-column solution may prove to be more efficient. Regards  Register To Reply

14. ## Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other) Originally Posted by XOR LX The equivalent would be:

=IF(COUNTIFS(\$A:\$A,A2,\$B:\$B,B2,\$C:\$C,-C2),IF(COUNTIFS(\$A\$2:\$A2,A2,\$B\$2:\$B2,B2,\$C\$2:\$C2,C2)=1,"DELETE",""),"")

in which at least you can reference entire columns with no detriment to performance.

I cannot guarantee that, over very large ranges, this set-up will not also suffer.

Regards
That's excellent, thank you.

I have tested it over 10,000 rows and it took around a minute and a half to process - literally hundreds of times faster than the SUMPRODUCT formula.

Thanks to everyone who took the time to look into this.

Have a very merry Christmas.  Register To Reply

15. ## Re: Converting a slow SUMPRODUCT formula to SUMIFS (or other)

You're welcome! And Merry Christmas!  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 