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

Hi,

Am Mon, 16 Apr 2018 03:42:59 -0700 (PDT) schrieb Håkan Björkström:

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.

your data in A1:G20. Then:

=INDEX(A1:G20,,MATCH(yourheader,A1:G1,0))


Regards
Claus B.
--
Windows10
Office 2016

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