## SUM, INDEX, MATCH for multiple columns

## SUM, INDEX, MATCH for multiple columns

(OP)

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

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

## RE: SUM, INDEX, MATCH for multiple columns

If line 7 contains asset specs, you could normalize your table in power query (unpivot table).

I don't know the structure of your data, but maybe calculated fields and calculated formulas in pivot table would help.

Using existing structure you may add helper columns and do some calculations in rows before summing up. But this approach is probably slow (+28k rows, 30 columns) and hard to manage if your data grow.

combo

## RE: SUM, INDEX, MATCH for multiple columns

You might consider uploading your workbook or a stripped down version.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: SUM, INDEX, MATCH for multiple columns

I will include a stripped down version of my data for your more detailed recommendations. I believe my match needs another or several more match statements.

Thank you

## RE: SUM, INDEX, MATCH for multiple columns

AssetSpecrange2) I named the range, B3:I37,

CostDataThe Sum of Cost for your row labels formula:

N5:

=SUMPRODUCT((AssetSpec=$M5)*(CostData))BUT this seems NOT to be representative of this statement of yours...

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: SUM, INDEX, MATCH for multiple columns

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: SUM, INDEX, MATCH for multiple columns

Thank you for your help.

## RE: SUM, INDEX, MATCH for multiple columns

I have no idea what you are referring to!

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: SUM, INDEX, MATCH for multiple columns

This is the part that gets all the errors, randomly.

(A_LCCP!$G$10:$G$28025=$A7) I did name the range, I just changed it to check for errors in the saving of the range.

Which equals your (AssetSpec=$M5)

## RE: SUM, INDEX, MATCH for multiple columns

## RE: SUM, INDEX, MATCH for multiple columns

The example I uploaded has no errors.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: SUM, INDEX, MATCH for multiple columns

1) AssetSpec is in column A and

2) the sum range includes column A?

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: SUM, INDEX, MATCH for multiple columns

Asset Spec is in column G "G10:G28025" on sheet A_LCCP. I did change my name and used your name "DataCost" for the $ columns R-AY, no Column G is not included in the DataCost="R10:AY28025" range.

=SUMPRODUCT((AssetSpec=$A7)*(DataCost))

## RE: SUM, INDEX, MATCH for multiple columns

DataCost, has anynon numeric valueyou’ll get a#value!Error.Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: SUM, INDEX, MATCH for multiple columns

Thank you!

## RE: SUM, INDEX, MATCH for multiple columns

You ought to be able to change the number format to General and see NO dollar character in your data. If you do, then there’s a problem.

Additionally, 1) Select the DataCost range 2) do a Find for a SPACE character. That could also give you an error.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: SUM, INDEX, MATCH for multiple columns

## RE: SUM, INDEX, MATCH for multiple columns

Like is said...staring right at me.

Thank you!!

It was my ranges, in all the back and forth checking and rechecking, I confused my AssetSpec with my DataCost and changed the reference cells in both.

It works now.

## RE: SUM, INDEX, MATCH for multiple columns

If this table is dynamic with respect to the number of rows and/or columns, you might want to write a macro to automatically redefine your named ranges as the range changes. If so, post a new thread in forum707: VBA Visual Basic for Applications (Microsoft), if you need help.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: SUM, INDEX, MATCH for multiple columns

## RE: SUM, INDEX, MATCH for multiple columns

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

## RE: SUM, INDEX, MATCH for multiple columns

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

Patty

## RE: SUM, INDEX, MATCH for multiple columns

combo