# [SOLVED] Sum index and match combination

1. ## [SOLVED] Sum index and match combination

I am trying to sum 12 columns based on looking up a reference that is in one column.

Basically I have 2 files where on both files Column A has a G/L account number. On the data file I have credits for each month going from column C to Column O. On the other I have one column where I want to bring in the sum of all the months based on looking up the G/L number in column A.

Any help you can give would be great if you have any questions please ask. Sorry I feel like this should be easy but I just can't think of what to do and since I don't know the correct function I can't search for it.

2. ## Re: lookup and sum combination

SUMIF comes to mind as a function, but it's hard to say exactly from you description. It's hard to picture. Click on GO ADVANCED and use the paperclip icon to attach a workbook.

In the workbook you post, point out the stuff of interest, perhaps even manually show a "result" you're looking for so we can see the goal line.

3. ## Re: lookup and sum combination

I think the problem with sumif is that there is not multiples of the number in column A. I can't really post the the spread sheet because it is sensitive financial info. I can dummy some stuff up tomorrow but I think for now we have a work around but we are trying to automate this and the workaround thows a glitch in it. For now a workaround will be to insert a column at the end of the months and sum them then run a vlookup to pull the sum in that column off the G/L number in column A

Here is a quick example of what I need to do:

So if I have in Sheet 1:
____A : B : C : D
1 G/L# : Jan : Feb : Mar
2 1001 : 50 : 50 : 50
3 1002 : 100 : 100 : 100

ON sheet 2 I would have:
____A : B
1 1001 : 150<--- Number I need a formula for (the sum of B,C,&D)
2 1002 : 300

4. ## Re: lookup and sum combination

Try...

B1, copied down:

=SUM(INDEX(Sheet1!\$B\$2:\$D\$3,MATCH(A1,Sheet1!\$A\$2:\$A\$3,0),0))

5. ## Re: lookup and sum combination

That worked thank you I will change title to solved.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1