sum a range using match and index

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
Hi
hope someone can point me in the right direction here. I have gleaned over the posts and may have missed the answer so here goes...

I have a number of worksheets with different bits of data on them but all have a common reference number and always in column A

I am trying to sum a row (say from BN:DF) based on a match and index formula.
these are my attempts.
  • =SUM(INDEX(Sales!BN:DF,MATCH(Sheet1!A2,Sales!A:A,0)))
  • =SUM(Sales!(match(A2,Sales!A:A,0)BN:(match(A2,Sales!A:A,0))))
It would appear that both are total drivel.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Welcome to the board :)

Are you trying to sum up the common references across all sheets? Or one sheet at a time?

If the first - what are the sheet names?
 
Upvote 0
If you're trying to sum one row between BN and DF, then your first formula is nearly right - you just need a zero as the column argument for index:

=SUM(INDEX(Sales!BN:DF,MATCH(Sheet1!A2,Sales!A:A,0),0))
 
Upvote 0
Whoa! I dont believe it - spot on thanks cornflakegirl
 
Upvote 0
How can I do the same for column
Welcome to the MrExcel board!

Something like this?

Excel Workbook
BCDEFGHI
2Header 1Header 2Header 3Header 4Header 5
3Header to FindCol Sum26576976
4Header 42433549875
547986
65587006
7469976
83796
947006
105425887
116586796
12768986
1377696786
14
Sum Column
 
Upvote 0
Hi,

Can any one please help me out...I want to add Billing for Sales person mention in Coloumn K ...I have tried combination of Sumif with Index & match..but may be I am missing something it shows as #NA#....

Following is the Data:

Billing Direct cost Margin %Sales person
27,60014000 13,60049%Aniket
312,900254875 58,02519%Caleb
254,100227382 26,71811%Caleb
75,0000 75,000100%Pradeep
50,0000 50,000100%Pradeep
273,0000 273,000100%Caleb
30,00021000 9,00030%I****a
30,00021000 9,00030%I****a
535,501394775 140,72626%Manish
250,005158125 91,88037%Caleb
112,5007000 105,50094%Manish
108,97571845 37,13034%Manish
6,0004000 2,00033%Manish
188,351126765 61,58633%I****a
53,81554131 -316-1%I****a
283,422189075 94,34733%I****a

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Tigress,

Welcome to the board.

Please start your own thread. Posting a question in a thread not started by you is considered hijacking. If you feel this thread is relevant to your issue, then post a link to it in your new thread.

Thanks.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top