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]

It seems that you may need different excel tools - pivot table and, if you have excel 2016, power query. This would make the whole project more structured and probably simpler. The first step: define data tables and work with tables.
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

 

[SkipVought]

Hi,

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



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pbundrant]

Hello combo and SkipVought,

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

 

[SkipVought]

1) I named your AssetSpec range
2) I named the range, B3:I37, CostData

The Sum of Cost for your row labels formula:
[tt]
N5: =SUMPRODUCT((AssetSpec=$M5)*(CostData))
[/tt]

BUT this seems NOT to be representative of this statement of yours...

The value of A7 is in many rows (770+).

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

This is perhaps a better [highlight #FCE94F]example[/highlight] of multiple rows and multiple columns.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pbundrant]

I'm getting a #VALUE error for every True instance

Thank you for your help.

 

[SkipVought]

????
I have no idea what you are referring to!

[pre]
Row Labels Sum of Cost
[highlight #FCE94F]Bath Tub $7,346.23[/highlight]
Bathroom Sink $-
Bathroom Sink Faucet $-
Bathtub Hardware $92.78
Boiler $-
Cabinetry $-
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pbundrant]

It's not even the true values, why would a #Value error occur when looking at text data?

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)

 

[pbundrant]

 

[SkipVought]

Again, I have absolutely no idea what you are referring to!

The example I uploaded has no errors.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Are you saying that in your full version,
1) AssetSpec is in column A and
2) the sum range includes column A?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pbundrant]

Hi Skip,

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

 

[SkipVought]

If the sum range, DataCost, has any non numeric value you’ll get a #value! Error.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pbundrant]

Yes, I verified all costs are Numbers, currency actually and all AssetSpec cells are text. No change.

Thank you!

 

[SkipVought]

Numbers are numbers. “Currency” is merely a number format.

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pbundrant]

Thank you Skip. I can't get it to work, after checking all the data, the ranges, and putting the formula in the data sheet, still no good. UGH! Makes no sense to me, I'm sure the problem is staring me in the face, it usually does.

 

[pbundrant]

LOL!!!

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.

 

[SkipVought]

Great!

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, if you need help.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pbundrant]

No it is not.