Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 16th 18, 11:42 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2017
Posts: 2
Default 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

  #2   Report Post  
Old April 16th 18, 11:59 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,619
Default Sumifs referring to columns defined by header

Hi,

Am Mon, 16 Apr 2018 03:42:59 -0700 (PDT) schrieb Hkan Bjrkstrm:

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   Report Post  
Old April 17th 18, 01:28 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2016
Posts: 12
Default 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 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Columns in Sum Range using SUMIFS kristikls Excel Worksheet Functions 1 March 9th 13 01:57 AM
sumifs multiple columns help duketter Excel Worksheet Functions 2 May 8th 10 08:44 AM
Referring to columns from within a macro Blobbies Excel Discussion (Misc queries) 4 August 9th 09 10:03 PM
referring to columns by number Stefi Excel Programming 2 November 17th 08 10:01 AM
Referring to non constant columns Jill1 Excel Programming 2 October 18th 06 08:43 PM


All times are GMT +1. The time now is 02:56 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017