SUM, INDEX, MATCH for multiple columns 1

[pbundrant]

Hello,

Can anyone please let me know what I am missing in my formula?

This is the formula that I have:

=SUM(INDEX(A_LCCP!$A$10:$A$28046,MATCH($A7,AssetSpec,0)):INDEX(A_LCCP!$AY$10:$AY$28046,MATCH($A7,AssetSpec,0)))

It works BUT! I need the sum of multiple columns. The value of A7 is in many rows (770+).
In case you need the column numbers: 18,19,20,21,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50

Thank you,
Patty

 

[combo]

What excel version do you have? If 2016, you can use the attached workbook solution directly. For 2013 it requires free microsoft PowerQuery com addin. Earlier versions don;t work.

With built-in excel tools, in the workbook:
1) power query (get & transform):
- reads data table,
- unpivots data to 3 column table,
-outputs transformed data to connection.
2) pivot table:
- connects to power query generated connection,
- summarises data.
Incase of more input worksheets you can extend pivot query model:
- duplicate processes for each sheet,
- add data category if specific for each sheet (as cost, income etc.) as 4th column,
- combine queries to single output,
- process output in pivot table.

No single formula required.

combo

 

[pbundrant]

Thank you Combo,

I got a solution for my issue. I use 2016 though. I will add PowerQuery here, I have it at home.

Patty

 

[combo]

I forgot to add: to update, just refresh your pivot table. NB. in excel 2016 pover query is already built-in (as Data > Get&Transform ribbon section).

combo