ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Sumifs referring to columns defined by header (https://www.excelbanter.com/excel-worksheet-functions/454058-sumifs-referring-columns-defined-header.html)

Håkan Björkström

Sumifs referring to columns defined by header
 
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

Claus Busch

Sumifs referring to columns defined by header
 
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

Roger Govier[_11_]

Sumifs referring to columns defined by header
 
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


All times are GMT +1. The time now is 11:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com