# sum of unique values in range

#### Eld0

##### New Member
Hello,

I've got a range of numbers on a sheet (in 1 column)

eg:
Column B
5.2
5.2
3.9
3.9
3.9
3.9
1.7
1.7
1.7
1.7

Now I want the sum of the unique values in another cell by using worksheetfunctions.

I know this can be done by applying a advanced filter and set the filter in list in-place and check unique option and then by calculating the SUBTOTAL of the not-hidden values.
But I want to do this with worksheetfunctions and not changing anything visible on the sheet. Can somebody help me?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### xld

##### Banned
Try

=SUM(IF((FREQUENCY(A2:A13,A2:A10)>0)*(A2:A10<>""),A2:A10))

#### Eld0

##### New Member
wow, can you explain a little bit what you are doing

#### Eld0

##### New Member
ok nvm how you did it. It works like a charm!! Just what I needed.

But I just got 1 remark: the function only works if I specify the exact range. Since it's part of an automatisation, I've got to keep in mind that more or less rows can be filled so I specify my range as follows 'A2:A1000' but then I get #N/A as answer (this is because all empty cells have #N/A to be summed up) How can I avoid this?

#### xld

##### Banned
This does it,

=SUM(IF(A2:A10<>"",IF(FREQUENCY(A2:A10,A2:A10)>0,A2:A10)))

but it is now an array formula.

#### Eld0

##### New Member
xld, it still gives me the same result: #N/A

I used the 'show calculations steps' and found out that the problem rises when evaluation the last parameter of the IF function: ',A2:A10)'
Because the FREQUENCY function ignores empty cells the array has less elements.
Can I be correct? I am a total newbie at using arrays in worksheetfunctions
The function works when I change the last parameter of the IF function in the exact range (leaving all other ranges as is) but I need to use the expanded range ',A2:A1000)'

#### xld

##### Banned
Did you array enter t (Ctrl-Shift-Enter)?

#### Eld0

##### New Member
ow yes it works now. I work already several years in excel and this is all new to me.
thanx man really I couldn't do this on my own!

best regards!

Replies
1
Views
328
Replies
3
Views
258
Replies
9
Views
141
Replies
0
Views
80
Replies
5
Views
145

1,191,684
Messages
5,987,998
Members
440,125
Latest member
vincentchu2369

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back