Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
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 |
#3
![]() |
|||
|
|||
![]()
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 don’t create a Table, but create names of Name, Amount1 and Amount2, just leave out Table1[ ] from the relevant cells. Regards Roger |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Multiple Columns in Sum Range using SUMIFS | Excel Worksheet Functions | |||
sumifs multiple columns help | Excel Worksheet Functions | |||
Referring to columns from within a macro | Excel Discussion (Misc queries) | |||
referring to columns by number | Excel Programming | |||
Referring to non constant columns | Excel Programming |