sum of unique values in range

[Eld0]

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?

Thanx in advance

 

[xld]

Try

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

 

[Eld0]

wow, can you explain a little bit what you are doing

thanx already for the quick reply

 

[Eld0]

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]

This does it,

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

but it is now an array formula.

 

[Eld0]

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]

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

 

[Eld0]

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!