Change SUMIFS "Sum Range" Column based on criteria - Any ideas?

[n0m0reg0dz]

Hi All,

This is my first post on this forum, where i have actually found many great ideas and solutions.

For my issue, i have tried looking into this forum and i could not find a satisfying solution, so i decided to try and ask giving as an example my problem, to see if there is anyone that can give me a better solution.

I have the below table (example)

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]VAT1[/TD]
[TD]VAT2[/TD]
[TD]VAT3[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD]A[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD]B[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD]C[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD]D[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD]B[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD]C[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD]Total[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]59[/TD]
[/TR]
</tbody>[/TABLE]

The numbers left of the letter represent the rows, while the B-C-D-E the columns.

I would like to create a sumifs that will allow me to change the Sum Range based on a criteria regarding the name of the Column, i.e. if my criteria is "VAT1", then the sumifs would be: =sumifs(C:C,B:B,"A") while if my criteria was "VAT2" the formula would be: =sumifs(D:D,B:B,"A")

Anyone has any idea on the above?

Thank you in advance,

George

 

[Jonmo1]

Try

=SUMIFS(INDEX(C$2:E$100,0,MATCH("VAT1",C$1:E$1,0)),B$2:B$100,"A")

 

[n0m0reg0dz]

That was very fast and very great!

Thank you for that, helped me a lot, it works perfectly!

 

[Jonmo1]

Glad to help, thanks for the feedback.