Simple Sum,,,,please help

[gokzee]

Hiiii,,

thanks for helping,

i need to find the sum of a column with the unique values in it and avoid #n/a

my column looks like this

column A

111
222
333
333
222
111
111
222
#n/a
#n/a
111

so basically i need to find the sum of 111+222+333 and avoid duplicates and #n/a

i cant remove the duplicates frm the sheet since it is linked.

Is there any formula ?

thanks in advance

gokz

[simeon.hruby]

In your formula in Column A use

Please Login or Register  to view this content.

This will eliminate the #N/A and replace it with nothing. Then you should be able to Sum everything normally.


Simeon

[gokzee]

thanks but

how can i use it with an array??

i am now using the formula {=(SUM(B2:B14*(1/COUNTIF(B2:B14,B2:B14)))}

thanks

gokz

[simeon.hruby]

Have you tried

Please Login or Register  to view this content.

Maybe try adding an IF statement in fromnt of the SUM. You are using Excel 2003 so the IFERROR function is not built in.

Let me know how this works.


Simeon

[:) Sixthsense :)]

Slight change in your formula to ignore error values and Text data

Array formula requires Ctrl+Shift+Enter

Please Login or Register  to view this content.

[vlady]

just to add you can also use isna

=sum(if(ISNA(A1:A12),0,A1:A12).......based on the given solutions above

[gokzee]

thank you sixth sense.

dat works.

just curious if u could help me with this logic


col A col B
121 #n/A
123 11
123 11
123 11
124 12
124 12
124 12
125 12
125 12

how can i get uniquely from column A and get the sum of column b as #n/a+11+12+12

hope u get it

thanks

gokz

[gokzee]

@simeon @vlady

thank you..

[:) Sixthsense :)]

Could you please let me know the output of the above one because I am little bit weak in maths

[gokzee]

sum of column B should be 35 removing all duplicates

[gokzee]

or according to the unique values in A how can i display the corresponding values in column B?

[:) Sixthsense :)]

Now it's clear

Again Slight Change in the previous formula

Array formula requires Ctrl+Shift+Enter

Please Login or Register  to view this content.

[gokzee]

wow..that works..

one more question if you dont mind!!

how can i display those unique numbers in colmn B corresponding to colmn A in a separate column C.

Hope u get it.

thnks

gokz

[:) Sixthsense :)]

Select cells C1 to C9 and with selection press = and paste the below formula

Please Login or Register  to view this content.

Requires Ctrl+Shift+Enter.

Change the B1:B9 to A1:A9 if you want to get the Unique Values from Column A1:A9.

[gokzee]

can i have an excel 2003 formula....i tried if(iserror.. and if(isna...

it returned error

thnks


gokz

[:) Sixthsense :)]

Regret for the inconvenience, forget to notice your excel version.

Please Login or Register  to view this content.

[gokzee]

it works well without iferror...

thanks a lot man....u r a genius in this...must say...

[Fotis1991]

My Congr to Sixthsense . Really nice work.

But @ gokzee

Your title is not says the TRUE.

Simple Sum.....

This is not a simple sum at all. Is it?

[gokzee]

@fotis i knw....but its still a simpe sum and got complicated when we had to display the numbrs.

sixth sense is a master in excel..

[:) Sixthsense :)]

My Congr to Sixthsense . Really nice work

Thanks Fortis

One congrats message from the expert is equivalent to 100 OP’s thanks message, now feeling it

[gokzee]

@sixth sense..thanks a lot..

still a long way to go with the project

[gokzee]

@sixth sense

True..