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.