Sum of unique criteria

[kyber]

Hello, using Office 365.
I need my sums to count numbers of unique identifiers. So if there are two numbers associated with the same ID, it needs to count it once instead of doubling it.

[TMS]

One way

Please Login or Register  to view this content.

[kyber]

It's not working. The return is #VALUE!

I modified to fit my cells.
=SUM(FILTER(UNIQUE($B$3:$H$200),{0,1}))

[TMS]

Well, that's not really surprising given that the sample you posted does not represent your actual file.

How are we supposed to guess what column you want to total?

[kyber]

So, should I upload a new file to paint a better picture of what I am working with?

[TMS]

No.

Try:

Please Login or Register  to view this content.

But, next time, ask the question you need answering.

[kyber]

For some reason, when I enter or even retype this formula it's inserting the apostrophe in front of the =. And I'm trying everything to get rid of it.

Edit:
I was able to get rid of the apostrophe, but for some reason the formula isn't working. Instead of giving an output it's as if the function is incomplete. I changed the values to suit my appropriate file.

Edit 2: I was able to figure out why it's not generating anything. When I removed the apostrophe it appeared to have automatically insert a space before the equal sign. Not sure why it's doing that.

[kyber]

I just uploaded the closest thing I could to the actual file. I generated generic identifiers and amounts. And based on your formula I modified to =SUM(INDEX(Table1[EOB Check Total],MATCH(UNIQUE(Table1[EOB/Batch Number]),Table1[EOB/Batch Number],0)))

As I just did it, I got a step closer and now it's just giving me #N/A

[T.I.]

Please try:

=SUMPRODUCT((MATCH($B$3:$B$69,$B$3:$B$69,)=(ROW($B$3:$B$69)-ROW($B$3)+1))*$H$3:$H$69)  

Btw, sum should be 305115 and not 314695. Checked via additional column.

[Bo_Ry]

Please try

=SUM(UNIQUE(IF({1,0},Table1[EOB/Batch Number],Table1[EOB Check Total])))

[nick.williams]

The blank rows are causing an error. Try:

=SUM(INDEX(Table1[EOB Check Total],MATCH(UNIQUE(FILTER(Table1[EOB/Batch Number],Table1[EOB/Batch Number]<>"")),Table1[EOB/Batch Number],0)))

[kyber]

You are right, my math was wrong. The formula works, but not completely. I notice that when I expand the range of the columns to include the vacant ones that the output goes back to #N/A.

[TMS]

The reason you get #N/A! is because of all the unnecessary blank rows in your Table. Delete them and the formula works. However, you have two other solutions that work regardless of whether or not there are blank rows. Note that none of the solutions give the value that you state you expect.


Kyber 305115 =SUM(INDEX(Table1[EOB Check Total],MATCH(UNIQUE(Table1[EOB/Batch Number]),Table1[EOB/Batch Number],0)))

Strogg 305115 =SUMPRODUCT((MATCH($B$3:$B$69,$B$3:$B$69,)=(ROW($B$3:$B$69)-ROW($B$3)+1))*$H$3:$H$69)

Bo_ry 305115 =SUM(UNIQUE(IF({1,0},Table1[EOB/Batch Number],Table1[EOB Check Total])))

[kyber]

@nick.williams that works, thank you so much!

[TMS]

Actually, only Bo_ry's solution works with blank rows out of those three. Nick's solution also caters for blank rows.

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[T.I.]

This formula works with blanks. In Excel 2016 requires CTRL+SHIFT+ENTER.

=SUM(IFNA((MATCH($B$3:$B$78;$B$3:$B$78;)=(ROW($B$3:$B$78)-ROW($B$3)+1))*$H$3:$H$78;))