sum a range using match and index

Thanks:  0
Likes:  0

Thread: sum a range using match and index

1. sum a range using match and index

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)BNmatch(A2,Sales!A:A,0))))
It would appear that both are total drivel.

2. Re: sum a range using match and index

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?

3. Re: sum a range using match and index

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))

4. Re: sum a range using match and index

Whoa! I dont believe it - spot on thanks cornflakegirl

5. Re: sum a range using match and index

How can I do the same for column

6. Re: sum a range using match and index

Originally Posted by spk26255
How can I do the same for column
Welcome to the MrExcel board!

Something like this?

Sum Column

 B C D E F G H I 2 Header 1 Header 2 Header 3 Header 4 Header 5 3 Header to Find Col Sum 2 65 76 9 76 4 Header 4 243 3 54 9 87 5 5 4 79 8 6 6 5 5 870 0 6 7 4 6 9 97 6 8 3 7 9 6 9 4 7 0 0 6 10 54 2 5 8 87 11 65 86 7 9 6 12 76 8 9 8 6 13 7 769 67 8 6 14

 Cell Formula C4 =SUM(INDEX(E3:I13,0,MATCH(B4,E2:I2,0)))

Excel tables to the web >> Excel Jeanie HTML 4

7. Re: sum a range using match and index

Thanks a ton. It solved my Purpose

8. Re: sum a range using match and index

Originally Posted by spk26255
Thanks a ton. It solved my Purpose
Cheers. Thanks for letting us know.

9. Re: sum a range using match and index

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,600 14000 13,600 49% Aniket 312,900 254875 58,025 19% Caleb 254,100 227382 26,718 11% Caleb 75,000 0 75,000 100% Pradeep 50,000 0 50,000 100% Pradeep 273,000 0 273,000 100% Caleb 30,000 21000 9,000 30% I****a 30,000 21000 9,000 30% I****a 535,501 394775 140,726 26% Manish 250,005 158125 91,880 37% Caleb 112,500 7000 105,500 94% Manish 108,975 71845 37,130 34% Manish 6,000 4000 2,000 33% Manish 188,351 126765 61,586 33% I****a 53,815 54131 -316 -1% I****a 283,422 189075 94,347 33% I****a

10. Re: sum a range using match and index

Tigress,

Welcome to the board.

Thanks.

User Tag List

Posting Permissions

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