×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here

Jobs

SUM, INDEX, MATCH for multiple columns

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,

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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           	 $-   
Cabinetry        	 $-   
 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: SUM, INDEX, MATCH for multiple columns

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: SUM, INDEX, MATCH for multiple columns

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

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close