Given an array, how can I index match each value and sum them without a helper column?

Question

I have multiple rows 34 columns wide with a reference Designator(RefDes) in each one that correlates to a value. I am trying to find a way to match each RefDes to its value and sum those together without the use of a helper cell for each column to give each row a total value.

My current formula will work with a helper cell but that adds 34 more columns and makes it very messy and hard to read.

=INDEX(FullFlow2[[Category Times]:[Category Times]],MATCH(N4,FullFlow2[[REFDES]:[REFDES]]))

Followed by a =sumproduct()in another column to collect the values.

Not sure if this is something I will need to use VBA for or if I can make it work without it.

This is the RefDesTable below

This is the FullFlow2 Table below

Please provide a visual representation of the data and expected output. It makes it easier to understand the question. — Scott Craner
– Scott Craner, Commented Jan 6, 2021 at 17:03
Pictures have been added, thank you! New user learning how to ask questions better so I appreciate the guidance! — JarvisButler290
– JarvisButler290, Commented Jan 6, 2021 at 17:11

Scott Craner · Accepted Answer · 2021-01-06 17:16:02Z

1

Use SUMIFS wrapped in a SUMPRODUCT:

=SUMPRODUCT(SUMIFS(FullFlow2[[Category Times]:[Category Times]],FullFlow2[[REFDES]:[REFDES]],YourFirstTableName[1st]))

Replace YourFirstTableName with the correct name for the table.

answered Jan 6, 2021 at 17:16

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Craner Over a year ago

Ahh, You wanted by row, I assumed column. Good job changing to your need.

JarvisButler290 Over a year ago

Got it work perfectly! Thank you, needed to go across the row though so I changed that a bit. Here is what I ended up using.

=SUMPRODUCT(SUMIFS(FullFlow2[[Category Times]:[Category Times]],FullFlow2[[REFDES]:[REFDES]],FullSNList__FBD[@[1st]:[34th]]))

Thank you for your help.

Collectives™ on Stack Overflow

Given an array, how can I index match each value and sum them without a helper column?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related