On Monday, 16 April 2018 11:43:03 UTC+1, Håkan Björkström wrote:
Hi

I need to use SUMIFS, where sum values and criteria values are in columns with column header (normal excel sheet). How can I refer to column headers instead of absolut address? Depending on the criteria value, different columns have to be calsulated.

rgds
Håkan

If you create Named ranges for your columns of data, or if you create a table, then you could achieve what you want with the use of INDIRECT.

Imagine range A1:A10 has a header of Name and a series of names such as a,b,c,d,a,d,b,c,b
Range B1:B10 has a header of Amount1 and a series of values
Range C1:C10 has a header of Amount2 and a series of values

If you created a Table of this data with a name of Table1, then if you put in cell
E1 Table1[Amount2]
F1 Table1[Name]
G1 a

Then the formula =SUMIFS(INDIRECT(E1),INDIRECT(F1),G1)
Will give your result.
Change the values in E1 and G1 and the result will adjust

If you dont create a Table, but create names of Name, Amount1 and Amount2, just leave out Table1[ ] from the relevant cells.

Regards
Roger