# 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.

2. ## Re: Sum of unique criteria

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

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}))

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?

5. ## Re: Sum of unique criteria

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

6. ## Re: Sum of unique criteria

No.

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

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.

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

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.

10. ## Re: Sum of unique criteria

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

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)))

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.

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])))

14. ## Re: Sum of unique criteria

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

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.

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.

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;))  ```

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1