Forum Discussion
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 C). How do I write a formula to sum the job value with the Machine name as the criteria and filtering through the job reference to only sum the value of that job once?
This is what I have so far but it's either returning 0 or the SPILL error:
=IF(B2:B350="Durst1",SUMPRODUCT(1/COUNTIF(C2:C350,D2:D350&""),D2:D350),0)
Many thanks for your help.
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)
18 Replies
I would create a helper column "count" to count the number of Machines. If the count is more than 1 then it should be 0 (using if function). then multiply your function by that count column. in this case you only sum values that have count of 1. A good article on this is here: https://dataempower.net/excel/sum-of-unique-values-in-a-dataset/
Would you like to only sum the first instance for each given job?
Patrick2788 Hello,
Yes, that would work. But specifically it would need to be the first instance on a machine not in total as the same job can be run on more than one machine, many times on that machine.
Thanks
To confirm, this means the value returned for "Durst 1" would be 845?
