Forum Discussion

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
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)
Book4 (2).xlsx52 KB

Patrick2788

Silver Contributor

Mar 16, 2020

A819A1L

Would you like to only sum the first instance for each given job?

A819A1L

Brass Contributor

Mar 16, 2020

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

Patrick2788
Silver Contributor
Mar 16, 2020
A819A1L
To confirm, this means the value returned for "Durst 1" would be 845?
- A819A1L
  Brass Contributor
  Mar 16, 2020
  Patrick2788 No, it would be £2,695.23. It would sum the value on row 3, 6 and 7.
  
  The screen shot is quite limiting. Attached is a spreadsheet with 350 lines. You can see there that the references are duplicated.
  
  Book4.xlsx18 KB
  - A819A1L
    Brass Contributor
    Mar 16, 2020
    I have used just tried this formula:
    
    SUMPRODUCT($D$2:$D$345,IF($C$2:$C$345="",0,1/COUNTIF($C$2:$C$345,$C$2:$C$345))*($B$2:$B$345="Durst1"))
    
    Which is returning the value of £14,052.68 for "Durst1", whereas it should be £20,873.55. I think this is because it's including the value of a job only once across all machines rather than individual machines.