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.

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

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.

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.

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.

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.