Forum Discussion

A819A1L's avatar
A819A1L
Brass Contributor
Mar 16, 2020
Solved

SUMIFS excluding duplicate values

Hello,   The goal is to sum the job value (column D) for each machine (column B). The issue is that a value will be included more than once because a job reference is listed more than once (column ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 16, 2020

    A819A1L 

    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)

foreverlearner's avatar
foreverlearner
Copper Contributor
Nov 18, 2020
@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
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 19, 2020

foreverlearner 

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.

Resources