Excel - SUM Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I'm trying to use something, like index/match, to find data then sum it across rows
Solved by M. F. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 04/04/2018 - 04:26
hi there!
User 04/04/2018 - 04:26
hello
Excelchat Expert 04/04/2018 - 04:27
Might you be able to show me a quick example of your data and what the end result should be?
User 04/04/2018 - 04:28
ok I have 12 months of expenses that I want to sum
Excelchat Expert 04/04/2018 - 04:28
your description makes total sense.. I think if I see exactly what you're working with it will help.
Excelchat Expert 04/04/2018 - 04:28
gotcha
User 04/04/2018 - 04:29
I was attempting to use an index/match to find the particular line item that I want to sum in a very large data set
User 04/04/2018 - 04:29
expenses are in the rows
Excelchat Expert 04/04/2018 - 04:29
smart
User 04/04/2018 - 04:29
so let's say I have manufacting expense of 5000 a month
User 04/04/2018 - 04:29
freight of 2000 a month
User 04/04/2018 - 04:29
and other of 1000 a month
Excelchat Expert 04/04/2018 - 04:30
kind of like that? ----->
User 04/04/2018 - 04:30
yes
Excelchat Expert 04/04/2018 - 04:30
cool
User 04/04/2018 - 04:30
I want to pull in the total of freight expense for the year into another tab in the workbook
User 04/04/2018 - 04:30
but not as a direct link because the row may change from month to month
Excelchat Expert 04/04/2018 - 04:31
ah yes
User 04/04/2018 - 04:31
I'm using an index match to pull in the monthly data from source file to the final report
User 04/04/2018 - 04:31
using an Analysis for Office crosstab as my datasource
Excelchat Expert 04/04/2018 - 04:31
looking up "Freight"?
User 04/04/2018 - 04:31
yes
Excelchat Expert 04/04/2018 - 04:32
in your source data... do you have a total column?
User 04/04/2018 - 04:32
no
Excelchat Expert 04/04/2018 - 04:32
are you able to add one? and then you could even hide it...
Excelchat Expert 04/04/2018 - 04:33
then the lookup can just reference that column
User 04/04/2018 - 04:33
yes I could do that. Is that the only way?
User 04/04/2018 - 04:33
does sumif only work on single columns? that was what I attempted initially
Excelchat Expert 04/04/2018 - 04:34
nope! i have another idea... it would use VLOOKUP a bunch of times
Excelchat Expert 04/04/2018 - 04:34
actually...
Excelchat Expert 04/04/2018 - 04:34
SUMIF is a sweet idea
Excelchat Expert 04/04/2018 - 04:34
lemme try something
User 04/04/2018 - 04:34
I thought about that as well, but vlookups degrade worksheet performance
User 04/04/2018 - 04:34
and this is a massive worksheet
Excelchat Expert 04/04/2018 - 04:34
this is true! if it's a larger workbook yes
Excelchat Expert 04/04/2018 - 04:34
hahahahaha
Excelchat Expert 04/04/2018 - 04:34
;)
User 04/04/2018 - 04:34
workbook...sorry
Excelchat Expert 04/04/2018 - 04:35
i gotcha
User 04/04/2018 - 04:35
it appears that sumif only works for a single column vs an array
Excelchat Expert 04/04/2018 - 04:35
i was worried about that...
Excelchat Expert 04/04/2018 - 04:35
give me a sec let me try something
Excelchat Expert 04/04/2018 - 04:35
and the freight line may move rows you said right?
User 04/04/2018 - 04:36
I figured there was a more complicated function tha I don't current know about that could work
User 04/04/2018 - 04:36
this example is very simplistic..there are 2000 other lines that could be in the report
User 04/04/2018 - 04:37
some may be blank until june so they wouldn't show up in the data set prior to that period and would potentially shift the lines down
Excelchat Expert 04/04/2018 - 04:37
right
User 04/04/2018 - 04:37
that's why I'm using index/match in my monthly data pull
Excelchat Expert 04/04/2018 - 04:37
my vlookup idea won't be good as there will be 12 nested VLOOKUPS which would be horrible for your sheet
User 04/04/2018 - 04:37
yep
Excelchat Expert 04/04/2018 - 04:38
i think the best bet is to just add that hidden column in there which totals each row for the year... then you can use index/match nicely and it won't matter which row the expense is in.. and this shouldn't slow down the file much as it's just a sum function
User 04/04/2018 - 04:39
so am I correct that sumif only works on single columns?
Excelchat Expert 04/04/2018 - 04:39
you're right about the SUMIF
User 04/04/2018 - 04:39
:-)
Excelchat Expert 04/04/2018 - 04:39
hahaha
Excelchat Expert 04/04/2018 - 04:39
you keep answering my thoughts before I hit enter!
User 04/04/2018 - 04:39
and there's no other function that can be applied that would sum an array vs a range?
Excelchat Expert 04/04/2018 - 04:39
is the new column a dealbreaker? Will that work for you or no?
User 04/04/2018 - 04:39
it isn't ideal
User 04/04/2018 - 04:40
if there's no other option then it is what it is
Excelchat Expert 04/04/2018 - 04:41
it is very clear that you know your stuff! I don't know of any other option, but I can't say that there isn't one. You are totally free to ping us back to pick someone else's brain... perhaps there is an agent who might know more than I. Might be worth a shot... but the column idea at least can be a failsafe
Excelchat Expert 04/04/2018 - 04:42
i checked online - nothing came up for me
User 04/04/2018 - 04:42
ok. thanks for your help!
Excelchat Expert 04/04/2018 - 04:42
my pleasure - sorry I couldn't come through 100%!
User 04/04/2018 - 04:43
I didn't know that about sumif..hadn't tried to use it on an array before
Excelchat Expert 04/04/2018 - 04:43
even if you make the formula an array formula... it still didnt work for me
Excelchat Expert 04/04/2018 - 04:43
CTL-SHIFT-ENTER im sure you know that trick
User 04/04/2018 - 04:44
i actually did not :-)
Excelchat Expert 04/04/2018 - 04:44
maybe give it a shot as I know Excel works different than google sheets
User 04/04/2018 - 04:44
I'm just a self taught explorer
Excelchat Expert 04/04/2018 - 04:44
so in your formula bar... click on the formula bar insode it.. then hold CTL and SHIFT and press ENTER
Excelchat Expert 04/04/2018 - 04:44
and see if the calculation works
Excelchat Expert 04/04/2018 - 04:44
ill mirror in excel just to test on my end
User 04/04/2018 - 04:45
didn't work for me
Excelchat Expert 04/04/2018 - 04:46
me neither i just get the single 2000
Excelchat Expert 04/04/2018 - 04:46
oh...
Excelchat Expert 04/04/2018 - 04:47
will the expense change amounts each month??
User 04/04/2018 - 04:47
yes
Excelchat Expert 04/04/2018 - 04:47
crap ha
User 04/04/2018 - 04:47
interesting finding :-)
Excelchat Expert 04/04/2018 - 04:48
was just going to multiply by 12
User 04/04/2018 - 04:49
there's gotta be a way to do this LOL
Excelchat Expert 04/04/2018 - 04:52
agreed... i am checking a few things
User 04/04/2018 - 04:53
i tried IMSUM
User 04/04/2018 - 04:54
didn't work LOL
Excelchat Expert 04/04/2018 - 04:55
does your INDEX/MACTH function bring back anything at all like the SUMIF does?
Excelchat Expert 04/04/2018 - 04:56
just January's total?
User 04/04/2018 - 04:56
yes
User 04/04/2018 - 04:58
actually, no it doesn't
Excelchat Expert 04/04/2018 - 04:58
hmmm.. maybe you could use your existing idea, but for the sum range you could just select one column. and then repeat that in the formula but just keep adding the totals as you reference each column Like (Index(match(blahblah) + (Index(match(blahblah) + (Index(match(blahblah) + (Index(match(blahblah)
Excelchat Expert 04/04/2018 - 04:58
that was my vlookup solution..
User 04/04/2018 - 04:58
yeah, I thought about that too
Excelchat Expert 04/04/2018 - 04:58
ohhh ok
Excelchat Expert 04/04/2018 - 05:01
trying one thing
Excelchat Expert 04/04/2018 - 05:02
i think i might have it
Excelchat Expert 04/04/2018 - 05:03
i have it!
Excelchat Expert 04/04/2018 - 05:03
let me send you this file
User 04/04/2018 - 05:03
I'm excited LOL
Excelchat Expert 04/04/2018 - 05:03
me too haha
Excelchat Expert 04/04/2018 - 05:05
[Uploaded an Excel file]
Excelchat Expert 04/04/2018 - 05:05
test by changing "Freight" to "Other" and then holding CTL-SHIFT and press enter
Excelchat Expert 04/04/2018 - 05:06
thats what activates the array functionality
User 04/04/2018 - 05:06
i had a feeling it needed to be a sum vs sumif
Excelchat Expert 04/04/2018 - 05:06
YES
Excelchat Expert 04/04/2018 - 05:06
you're right
Excelchat Expert 04/04/2018 - 05:06
the IF within the SUM
User 04/04/2018 - 05:11
working in my index/match and iferror
Excelchat Expert 04/04/2018 - 05:11
yes!
Excelchat Expert 04/04/2018 - 05:11
do you even need the index/match?
User 04/04/2018 - 05:12
not sure yet :-)
Excelchat Expert 04/04/2018 - 05:12
I'm thinking not
User 04/04/2018 - 05:15
didn't work
User 04/04/2018 - 05:15
troubleshooting now
Excelchat Expert 04/04/2018 - 05:15
cool
User 04/04/2018 - 05:16
got it!!!!!!!!!
User 04/04/2018 - 05:16
without the index match
Excelchat Expert 04/04/2018 - 05:17
yes!! very very nice. i didn't think you'd need it anymore
Excelchat Expert 04/04/2018 - 05:17
so sorry this took so long!
User 04/04/2018 - 05:17
I think we both learned something, so it was worth it :-)
Excelchat Expert 04/04/2018 - 05:17
100% friend!
User 04/04/2018 - 05:17
thanks so much
User 04/04/2018 - 05:17
have a great day
Excelchat Expert 04/04/2018 - 05:18
my pleasure, thank you as well. Super fun problem
Excelchat Expert 04/04/2018 - 05:18
you also.
User 04/04/2018 - 05:18
one more question - every time I click on the formula, the array goes away...how do I make it fixed?
Excelchat Expert 04/04/2018 - 05:18
you have to press CLT SHIFT ENTER each time its very annoying
User 04/04/2018 - 05:19
that's going to suck for an end user of a delivered report
User 04/04/2018 - 05:19
no way to make it stay that way
User 04/04/2018 - 05:19
?
Excelchat Expert 04/04/2018 - 05:20
would the reason that they are clicking in the formula be to change the expense category?
Excelchat Expert 04/04/2018 - 05:20
like they have to actually click the cell, then click in the formula bar for it to go away
User 04/04/2018 - 05:20
ah ok. only changes if you click the formula
User 04/04/2018 - 05:20
blem
User 04/04/2018 - 05:20
no problem
Excelchat Expert 04/04/2018 - 05:20
yeah
Excelchat Expert 04/04/2018 - 05:20
ok wicked
Excelchat Expert 04/04/2018 - 05:20
which you could even lock
User 04/04/2018 - 05:22
yep. this has been super helpful. thanks again
Excelchat Expert 04/04/2018 - 05:22
my pleasure! take care all the best

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.