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)

	B	C	D	E
		VAT1	VAT2	VAT3
3	A	5	6	7
4	B	6	7	8
5	C	8	9	12
6	D	14	23	14
7	A	2	5	7
8	B	4	5	8
9	C	8	5	3
10	Total	47	60	59

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

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.