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)

A819A1L's avatar
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.

Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Mar 16, 2020

A819A1L 

Which is returning the value of £14,052.68 for "Durst1", whereas it should be £20,873.55.

That can be achieved by a simple SUMIFS():

=SUMIFS($D$2:$D$342,$B$2:$B$342,F345)

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 16, 2020

    Detlef_Lewin , that doesn't exclude duplicates in column C

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Mar 16, 2020

      SergeiBaklan 

      Well, it's the desired result.

      Perhaps it would have been better if the thread opener had given the desired results for the other machines as well.

       

      • A819A1L's avatar
        A819A1L
        Brass Contributor
        Mar 17, 2020

        Hi All,

         

        Thank you for your input! Both solutions work well.

         

        Apologies if I wasn't clear enough, it's a new problem that I hadn't come across before and wasn't sure how to explain it.  The reason why I need a formula to calculate the result is because the master spreadsheet I am using will be updated over several days.  The desired outcome is to have a summary page for all machines that would update with any new data.

         

        Thanks again.

Resources