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.
