Variable Column Reference in Formula

[jacobk57]

How can I use the sumifs function using a variable column reference determined by the match function.

Here are the formula's manually
=sumifs(Z:Z,I:I,1)
=sumifs(AB:AB,I:I,1)
=sumifs(AR:AR,I:I,1)

I want to replace the "Z:Z" "AB:AB" and "AR:AR" with a variable column reference that is automatically calculated by the Match function?

Hopefully my question makes sense.
Jacob

 

[Eric W]

Welcome to the board.

That can be done, but it depends on your layout. I see the 3 columns you want are not adjacent. Nor do you say what you are using MATCH to find. A header? Here's one option:

=SUMIFS(CHOOSE(J1,Z:Z,AB:AB,AR:AR),I:I,1)

You'd replace J1 with the result of your MATCH, if it's 1, 2, or 3.


If you are matching a header, on an adjacent group of columns, then maybe:

=SUMIFS(INDEX(Z:AR,,MATCH(J1,Z1:AR1,0)),I:I,1)

where J1 is the header you're looking for, and you're looking in Z1:AR1.

 

[jacobk57]

Thanks! However I am getting an error. When I look at the formula in the Excel "function arguments" box the results of the area are in quotes.

I am wondering the result of the formula is returning the value "Z:Z" instead of Z:Z and the Excel formula does not know how to handle the quotes.

https://www.dropbox.com/s/7sdmnqsvth3pyod/Capture.PNG?dl=0

 

[Eric W]

What is the formula you're using? And ideally, can you show the value of the cells you're referencing? According to that screen print, somehow you are giving the CHOOSE a range of A8:B8. It should be a whole column reference, A:A, or Z:Z.

 

[jacobk57]

I re-looked at your second formula and it worked!

=SUMIFS(INDEX(Z:AR,,MATCH(J1,Z1:AR1,0)),I:I,1)

Thanks so much!

 

[Eric W]

You're welcome!