**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.*