×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# 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

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

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

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

Hi,

Skip,

Just traded in my OLD subtlety...
for a NUance!

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

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

The 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... #### Quote: The value of A7 is in many rows (770+). Skip, Just traded in my OLD subtlety... for a NUance! ### RE: SUM, INDEX, MATCH for multiple columns (OP) I'm getting a #VALUE error for every True instance :( Thank you for your help. ### RE: SUM, INDEX, MATCH for multiple columns ???? I have no idea what you are referring to!  Row Labels Sum of Cost Bath Tub$7,346.23
Bathroom Sink    	 $- Bathroom Sink Faucet$-
Bathtub Hardware	 $92.78 Boiler$-

Which equals your (AssetSpec=$M5) ### RE: SUM, INDEX, MATCH for multiple columns (OP) ### RE: SUM, INDEX, MATCH for multiple columns Again, I have absolutely no idea what you are referring to! The example I uploaded has no errors. Skip, Just traded in my OLD subtlety... for a NUance! ### RE: SUM, INDEX, MATCH for multiple columns Are you saying that in your full version, 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 (OP) 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))

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

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

Skip,

Just traded in my OLD subtlety...
for a NUance!

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

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

Thank you!

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

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,

Just traded in my OLD subtlety...
for a NUance!

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

(OP)
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.

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

(OP)
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.

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

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: VBA Visual Basic for Applications (Microsoft), if you need help.

Skip,

Just traded in my OLD subtlety...
for a NUance!

(OP)
No it is not. :)

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

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

(OP)
Thank you Combo,

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

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!