Forum Discussion
Solved
Calculate the count without duplicates
I am looking for a formula to calculate the count without duplicates for unique combinations of "Country," "Code," and "Serial"
The result for country Mexico and Code 0014 should be 4 because it has 4 different Serial and for Canada's Code 0206 should be 3 (Three different Serial).
Can you please help with a formula to have this calculation?
| Customer Country Description | Code | Serial | Resultad |
| Mexico | 0014 | X | 4 |
| Mexico | 0014 | Y | 4 |
| Mexico | 0014 | Y | 4 |
| Mexico | 0014 | Z | 4 |
| Mexico | 0014 | Z | 4 |
| Mexico | 0014 | W | 4 |
| Canada | 0206 | A | 3 |
| Canada | 0206 | B | 3 |
| Canada | 0206 | C | 3 |
Thanks.
JAnderson910 The problem is definitely a result of the formula referencing cell D30, which is the next row located outside of the table. When a new table row is inserted by using the Tab key, the relative cell reference is affected in the same manner that inserting a new row in the middle of the table would result in inconsistent cell references. One way to solve this problem is to define a name in Name Manager using a relative row reference...
Start by selecting cell H29, for example. On the ribbon, go to Formulas > Define Name. Name it "NextMeterRead", then in the Refers To field, select cell D30, then press F4 on your keyboard twice to make it a relative row reference ($D30). It should look something like this:
Define Name: NextMeterRead
Once defined, you can then update your calculated column formula as follows:
=IF(NextMeterRead-[@[Sump Meter]]>-1, NextMeterRead-[@[Sump Meter]], "")Or, as mentioned in my previous reply, the MAX function could also do the trick:
=MAX(NextMeterRead-[@[Sump Meter]], 0)NextMeterRead will always refer to the row below the current row, in Column D, regardless of how you go about inserting a new row. Cheers!
5 Replies
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
=BYROW(A2:C10,LAMBDA(y,COUNTA(UNIQUE(TAKE(FILTER($A$2:$C$10,($A$2:$A$10=TAKE(y,,1))*($B$2:$B$10=CHOOSECOLS(y,2))),,-1)))))
With Office 365 or Excel 2021 or Excel for the web you can apply this formula.
- Hi, thanks a lot.
Wow, that formula is very complex jeje. Would it be possible to do something with sumproduct and countifs for instance?
Thanks!=MAP(A2:A10,B2:B10,LAMBDA(customer,code,COUNTA(UNIQUE(FILTER(C2:C10,(A2:A10=customer)*(B2:B10=code))))))
You are welcome. This formula is a little bit shorter.
Unfortunately i don't know how we can do this with SUMPRODUCT and COUNTIFS and without LAMBDA, UNIQUE functions.
- The desired formula should go for each row as shown in the table above on column "Resultad"
