sum, index, match, for multiple look up array

1. sum, index, match, for multiple look up array

Hi all,

I have been using a standard Index,Match,Match formula, works great, however the formula can get very long. I am looking for a way to shorten it, by selecting the array of values I want to be retrieved.

In the spreadsheet example: Cell N7 produces the desired result, but can lead to novel style formulas if I have 30+ records I am trying to sum

Any ideas as to how I can shorten the formula, preferably to be able to select the range M8:M12

Thanks

2. Re: sum, index, match, for multiple look up array

Here is a better formula to sum range of cells for revenue
Formula:
`Please Login or Register  to view this content.`

Or this
Formula:
`Please Login or Register  to view this content.`

Or this
Formula:
`Please Login or Register  to view this content.`

If you want to reference N6 cell use formula below
Formula:
`Please Login or Register  to view this content.`

3. Re: sum, index, match, for multiple look up array

Hi AlKey, Thanks for the quick response.

Something I should have mentioned, cell N6 would be a combo Box/ In List selection, so I would need to Index all columns, right now cell N6 is referencing Revenue, however any change to the reference would need to sum a column other than D:D.

Thanks,
Dean

4. Re: sum, index, match, for multiple look up array

Or try this ...

=SUMPRODUCT(--(COUNTIF(\$M\$8:\$M\$12,\$C\$6:\$C\$17)>0),OFFSET(\$C\$6:\$C\$17,,MATCH(N6,\$D\$5:\$J\$5,0)))

5. Re: sum, index, match, for multiple look up array

Thanks both, Phuocam, this works wonderfully. thank you!

6. Re: sum, index, match, for multiple look up array

You're welcome!

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