Forum Discussion
SUMIFS excluding duplicate values
Couple of more variants if only I understood the logic correctly
with
=SORT(UNIQUE(FILTER(B2:B350,B2:B350<>"")))and
=SUM(INDEX($D$2:$D$350,XMATCH($F345&UNIQUE(FILTER($C$2:$C$350,$B$2:$B$350=$F345)),$B$2:$B$350&$C$2:$C$350,0)))Another one is simple Power Query (Sheet2)
This formula is returning the SPILL error:
IF($B$2:$B$345="Durst1",SUMPRODUCT($D$2:$D$345,IF($C$2:$C$345="",0,1/COUNTIF($C$2:$C$345,$C$2:$C$345))),"")
Couple of more variants if only I understood the logic correctly
with
=SORT(UNIQUE(FILTER(B2:B350,B2:B350<>"")))and
=SUM(INDEX($D$2:$D$350,XMATCH($F345&UNIQUE(FILTER($C$2:$C$350,$B$2:$B$350=$F345)),$B$2:$B$350&$C$2:$C$350,0)))Another one is simple Power Query (Sheet2)
I don't remember details, but SUMIFS() doesn't work here since we shall exclude duplicates. Thus SUMPRODUCT() pattern or other formulas. With introduced dynamic arrays related functions are more suitable for such task. Power Query is even more natural here, but it requires refresh.
In general, if few solution exists, that's only you own decision. For me, if performance is not a bottleneck, main reason is how easy it'll be with such solution on maintenance phase. Is it routine and well known approach for me or some exotic pattern; will the workbook be distributed to other people and, if so, how it is compatible with their environment, how easy it'll be to customize when necessary; etc.
- @Sergeri Sir, Yes you are right, I downloaded again, not sure what happened and I could able to do the same to my data in power query. Is there a specific reason you have taken this approach of unique, index match ? what is the advantage of this over Sumif () and copying it as value and deleting the duplicates. Thanks a ton
Hi Sergei, I am trying the same, unfortunately there is no sheet 2 in the solution and I am keen to get the power query solution.
