How to sum values and remove duplicates rows

Anatoliy1992 Hi

Yet another alternative is doing it on the worksheet using SUMIFS and, assuming 365, UNIQUE.

Two versions;

G2 =UNIQUE(A2:C10) results in the unique combinations of number client and date.

M2 =UNIQUE(A2:A10) results in only unique item numbers and could be functional if an item number should have just one combination of clients and dates (I doubt it in this case but the it would be any version of lookup, preferrably xlookup, in n2 and o2)

What could be tricky is addressing item number, client and date individually in the three columns wide spill area g2#, but one way is using the operator space, resulting in a range common for two ranges. Space is like @ but not for only one cell*.

=SUMIFS(D:D,A:A,G2# G:G,B:B,G2# H:H,C:C,G2# I:I)

G2# is in this case same as G2:i7

G2:i7 H:H equals H2:H7

The sumifs above is like

sum D:D if A:A equals G2:G7 and B:B equals H2:H7 and C:C equals i2:i7

A difference against Power Query is that the result is updated every time data is changed.

If input is another file, eg a csv export from another system, Power Query is handy to get the transformation done when the file is loaded and thus calculated only once per import - not every time something is changed on the worksheet.

* @ can be seen as a function and as such could have been called and displayed as SINGLE()