Excel - SUM Function Problem - Expert Solution

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
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
Excelchat Expert 17/03/2018 - 09:57
This is the file at the preview
Excelchat Expert 17/03/2018 - 09:57
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.