**Question description:**

*This user has given permission to use the problem statement for this blog.*

i need a formula to return and sum unique items from a list

Solved by I. E. in 59 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
17/03/2018 - 09:12

Hello

User
17/03/2018 - 09:13

G'Day how area I'm Steve

Excelchat Expert
17/03/2018 - 09:14

What do you want to sum

User
17/03/2018 - 09:16

ok so currently in my example i hand wrote the names in D12:D15 but i want a formula to return these unique values from the list in B:B

User
17/03/2018 - 09:17

these are subtotals i want the formula to list these names and then i need the formula in E12:E15 to go in the cells next to how ever many names end up in the list

User
17/03/2018 - 09:17

does this make sense to you?

Excelchat Expert
17/03/2018 - 09:17

You need to get the unique value or the names form column B ?

User
17/03/2018 - 09:17

i need to list the unique names

Excelchat Expert
17/03/2018 - 09:18

Ok

User
17/03/2018 - 09:18

as i have written in D12:D15

Excelchat Expert
17/03/2018 - 09:18

Ok i see

Excelchat Expert
17/03/2018 - 09:27

Done

Excelchat Expert
17/03/2018 - 09:27

Check the formula

User
17/03/2018 - 09:28

ok hang on ill look

User
17/03/2018 - 09:29

ok hang on let me try something

Excelchat Expert
17/03/2018 - 09:29

Ok

User
17/03/2018 - 09:31

wow thats great now all i need is for the formula between E12:E15 to appear in the cell next to it so that no matter how many unique names are returned

Excelchat Expert
17/03/2018 - 09:31

Yes

User
17/03/2018 - 09:31

so i have an idea ill change the sheet and show you

Excelchat Expert
17/03/2018 - 09:31

Ok

User
17/03/2018 - 09:34

can you see what i am trying to do?

User
17/03/2018 - 09:34

put the unique names list in its own column and the grand total in its own column

Excelchat Expert
17/03/2018 - 09:35

You mean column F and G

User
17/03/2018 - 09:35

yes from C and D 12:15

Excelchat Expert
17/03/2018 - 09:36

Ok

User
17/03/2018 - 09:36

and then H sums all in F:F

Excelchat Expert
17/03/2018 - 09:36

But you have to add a top row

User
17/03/2018 - 09:36

i mean all in G:G

Excelchat Expert
17/03/2018 - 09:36

Heading

User
17/03/2018 - 09:36

why do i need a heading?

User
17/03/2018 - 09:36

for the title

Excelchat Expert
17/03/2018 - 09:36

Yes

User
17/03/2018 - 09:36

ok

Excelchat Expert
17/03/2018 - 09:37

And the formula is used one cell up

User
17/03/2018 - 09:37

i don't understand?

Excelchat Expert
17/03/2018 - 09:39

Notice that the formula at cell F2 is using the cell F1

User
17/03/2018 - 09:39

what is it using F1 for?

User
17/03/2018 - 09:39

what is it referencing that for

Excelchat Expert
17/03/2018 - 09:39

It is a variable range

User
17/03/2018 - 09:40

oh for the countif?

Excelchat Expert
17/03/2018 - 09:40

it change as the you drag the formula

Excelchat Expert
17/03/2018 - 09:40

Yes

User
17/03/2018 - 09:40

ok

Excelchat Expert
17/03/2018 - 09:40

For the countif

User
17/03/2018 - 09:40

ok so this might help you understand why i put them in seperate columns

User
17/03/2018 - 09:41

A:A is on a different spreadsheet and users can add titles into A:A

User
17/03/2018 - 09:41

they will also add to B:B and C:C

Excelchat Expert
17/03/2018 - 09:42

Yes

User
17/03/2018 - 09:42

my sheet will return all the data from the other sheet where users have added data into A:A,B:B, and C:C

Excelchat Expert
17/03/2018 - 09:42

There is no problem with this

User
17/03/2018 - 09:42

because this is a ever changing column in the other sheet i needed a way to list however many unique names i find

Excelchat Expert
17/03/2018 - 09:42

You can apply it for another sheet.

User
17/03/2018 - 09:43

having these in a seperate column helps with this

User
17/03/2018 - 09:43

think that now all i have to do is drag the formula in both F:F and G:G down say 500 rows and that should allow me enough rows of unique names out a list of about 2000

Excelchat Expert
17/03/2018 - 09:44

That's right .

Excelchat Expert
17/03/2018 - 09:45

All you have to do is to drag the formula for all your rows.

User
17/03/2018 - 09:45

aha i thought i was right, so now i can delete C and D 12:15 right?

Excelchat Expert
17/03/2018 - 09:45

Yes

Excelchat Expert
17/03/2018 - 09:45

But you have to edit the formula at col G

User
17/03/2018 - 09:46

yes i understand

Excelchat Expert
17/03/2018 - 09:46

It is not showing the same values as D12:D15

User
17/03/2018 - 09:46

actually when i put this all in my other sheet i will have to edit the formula in each cell to match the actual name locations which are on other sheets and such

Excelchat Expert
17/03/2018 - 09:47

Edited

Excelchat Expert
17/03/2018 - 09:47

Note that this is an array formula

User
17/03/2018 - 09:47

ok does this affect anything being an array?

Excelchat Expert
17/03/2018 - 09:48

So when you apply this at your excel you have to press ctrl+shift+enter

User
17/03/2018 - 09:48

ok what does that do

Excelchat Expert
17/03/2018 - 09:48

and use this formula

Excelchat Expert
17/03/2018 - 09:48

=iferror(INDEX($A$2:$A$11,MATCH(0,COUNTIF($F$1:F1,$A$2:$A$11),0),1),"")

Excelchat Expert
17/03/2018 - 09:49

whithout the ArrayFormula(

User
17/03/2018 - 09:49

now I'm confused

Excelchat Expert
17/03/2018 - 09:49

Got it?

User
17/03/2018 - 09:49

oh ok why is that

Excelchat Expert
17/03/2018 - 09:50

Because the formula ArrayFormula() doesn't work with excel

User
17/03/2018 - 09:50

oh gosh i didn't know that

Excelchat Expert
17/03/2018 - 09:51

It just work with online sheets

User
17/03/2018 - 09:51

can you check for me if here is anything else that might not work with excel?

Excelchat Expert
17/03/2018 - 09:51

So instead this formula you have to press ctrl+shift+enter

Excelchat Expert
17/03/2018 - 09:52

Just this

Excelchat Expert
17/03/2018 - 09:52

So.

Excelchat Expert
17/03/2018 - 09:52

What you have to do is >>

User
17/03/2018 - 09:52

ok so i have copied the one you said to a notepad

User
17/03/2018 - 09:52

then i will paste that into excel

Excelchat Expert
17/03/2018 - 09:52

paste this formula

Excelchat Expert
17/03/2018 - 09:52

iferror(INDEX($A$2:$A$11,MATCH(0,COUNTIF($F$1:F1,$A$2:$A$11),0),1),"")

User
17/03/2018 - 09:53

yes that

Excelchat Expert
17/03/2018 - 09:53

paste it on your excel and press ctrl+shift+enter

User
17/03/2018 - 09:53

so just to be clear this is the formula that lists the unique names

User
17/03/2018 - 09:53

ok i understand

Excelchat Expert
17/03/2018 - 09:53

And all will work very well

User
17/03/2018 - 09:53

do you mind if i try it quickly in case i make an error

Excelchat Expert
17/03/2018 - 09:54

of course no

Excelchat Expert
17/03/2018 - 09:54

Try this

Excelchat Expert
17/03/2018 - 09:54

we have 18 mins

User
17/03/2018 - 09:54

ok it won't take me long i will just try the example we have

User
17/03/2018 - 09:55

im not going to paste it into my final work just yet i just need a proof of concept

User
17/03/2018 - 09:55

brb

Excelchat Expert
17/03/2018 - 09:56

[Uploaded an Excel file]

Excelchat Expert
17/03/2018 - 09:57

This is the file at the preview

Excelchat Expert
17/03/2018 - 09:57

You can download it and see the formula applied

User
17/03/2018 - 09:58

ok hang on ill try that as it didn't work in excel for me yet

User
17/03/2018 - 10:00

ok i still see a different problem

User
17/03/2018 - 10:00

ou adjust the formula to say look in A2 until :A

User
17/03/2018 - 10:01

do you understand what i mean

User
17/03/2018 - 10:01

because i tried adding a name in A:A and it didn't work when i dragged the formula down in F:F

Excelchat Expert
17/03/2018 - 10:02

used the same formula

Excelchat Expert
17/03/2018 - 10:02

and used the $ sign with the ranges

Excelchat Expert
17/03/2018 - 10:02

press ctrl+shift+enter

Excelchat Expert
17/03/2018 - 10:03

check all of this

User
17/03/2018 - 10:03

yes i noticed you have the range of A:A set to 2:12 or something can you make it the whole column

User
17/03/2018 - 10:03

ld that be $A2:A?

Excelchat Expert
17/03/2018 - 10:04

You need to all the column A

Excelchat Expert
17/03/2018 - 10:04

I make it just for the data inserted on col A

User
17/03/2018 - 10:04

ok

User
17/03/2018 - 10:05

can you tell it to look in all column A:A

Excelchat Expert
17/03/2018 - 10:05

Because it will take more time if you apply it for all the rows

Excelchat Expert
17/03/2018 - 10:05

Ok

User
17/03/2018 - 10:05

can i try something

Excelchat Expert
17/03/2018 - 10:05

I can modify it

Excelchat Expert
17/03/2018 - 10:05

Ok

User
17/03/2018 - 10:05

is that correct in cell F2

User
17/03/2018 - 10:06

so i think we need to change the reference in the index and the countif correct?

Excelchat Expert
17/03/2018 - 10:07

Yes

User
17/03/2018 - 10:07

ahah your a clever guy!!

Excelchat Expert
17/03/2018 - 10:07

I'll modify it

User
17/03/2018 - 10:07

thank you

User
17/03/2018 - 10:11

are you nearly done as my time is running out?

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*