We help IT Professionals succeed at work.

Combining Indirect with Match

Hello,

I'm trying to combine match with indirect so that it sums up the entire column once it matches a text value in a particular but I've been having trouble getting it to work.

Help would be greatly appreciated.

Thanks.

=SUM((INDIRECT(MATCH(B250,5:5,0)&":"&MATCH(B250,5:5,0),TRUE)))

Open in new window

Comment
Watch Question

NorieAnalyst Assistant

Commented:
What exactly do you want to sum?

Your 2 MATCH formulas are going to return the column number where the value in B250 is found in row 5.

For example if the value in B250 was found in column 5 you would be summing row 5.

Author

Commented:
I'm trying to sum the entire column.
NorieAnalyst Assistant

Commented:
The entire column where you find the value from B250 in row 5?

You could try something like this.

=SUM(INDEX(A:Z,,MATCH(B250,5:5,0)))
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You may also try something like this...

=SUM(INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(B250,5:5,0),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,MATCH(B250,5:5,0),4),"1","")))

Open in new window

Rob HensonFinance Analyst

Commented:
You can create the SUM range with an OFFSET function:

=SUM(OFFSET($A$5,0,MATCH($B$250,$5:$5,0)-1,1048571,1))

Syntax of OFFSET is:

=OFFSET(Reference Point, Row Offset, Column Offset, Height, Width)

Reference Point - a known starting point, in this case you know your headings are in row 5 so the starting point can be A5
Row Offset - the headers are in row 5 so set to zero so no row offset
Column Offset - this is the unknown factor so is determined by matching B250 in row 5; the offset will then offset by that number from column A so needs reducing by 1 to get correct column.
Height - the number of rows to include in the sum, I have set to the absolute maximum (max rows of 1048576 less 5 as you are starting on row 5)
Width - just summing one column so set to 1.

Author

Commented:
Perfect thank you very much.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!
Thanks for the feedback.