# Sum of unique criteria

1. ## Sum of unique criteria

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.  Register To Reply

2. ## Re: Sum of unique criteria

One way
Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Sum of unique criteria

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

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

4. ## Re: Sum of unique criteria

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?  Register To Reply

5. ## Re: Sum of unique criteria

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

6. ## Re: Sum of unique criteria

No.

Try:
Formula:  `Please Login or Register  to view this content.`  Register To Reply

7. ## Re: Sum of unique criteria

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.  Register To Reply

8. ## Re: Sum of unique criteria

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  Register To Reply

9. ## Re: Sum of unique criteria

PHP Code:
``` =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.  Register To Reply

10. ## Re: Sum of unique criteria

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

11. ## Re: Sum of unique criteria

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)))  Register To Reply

12. ## Re: Sum of unique criteria

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.  Register To Reply

13. ## Re: Sum of unique criteria

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])))  Register To Reply

14. ## Re: Sum of unique criteria

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

15. ## Re: Sum of unique criteria

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

16. ## Re: Sum of unique criteria

New quick method:

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
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.  Register To Reply

17. ## Re: Sum of unique criteria

This formula works with blanks. In Excel 2016 requires CTRL+SHIFT+ENTER.
PHP Code:
``` =SUM(IFNA((MATCH(\$B\$3:\$B\$78;\$B\$3:\$B\$78;)=(ROW(\$B\$3:\$B\$78)-ROW(\$B\$3)+1))*\$H\$3:\$H\$78;))  ```  Register To Reply