Conditial Sum

[krissz]

I have been tasked with providing a 'flexible' spreadsheet for analysing sales figures. Resolving by product, sales margins, etc has not proced a problem. However, the data set covers several years. What I have been trying to generate a conditional sum based on two dates entered by the user. As there are multiple columns for the various products, I am looking for a generic solution based on cells in the sheet rather than hard code the column. My currnet attempt is:
=SUMIFS(column(), S_Date," >="&$AG$3, S_Date,"<="&$AG&4)
The variables (as a ref to a cell) include:
- S_Date is a variable date range (Sales Date) to allow for addition of later data;
- Start & End dates and the relevant rows as above to be summed (solved);
- Column to Sum. ??? Have tried COLUMN(), Column No in SUMIFS() plus other ideas.
I am sure that there is a solution, but cannot locate it.

 

[jasonb75]

You need to use either INDEX or OFFSET to define the column. From your post it is not clear if you want to sum a single column which is to be decided by other factors, or to sum all columns in a specified range.

 

[krissz]

Thanks
I need to sum all the columns individually. I am using Excel 365
I have tried both INDEX & OFFSET without much success. The only working function used INDIRECT but had to hard code the column.
=SUM(INDIRECT("AS"&AI3+1&":AS"&AI4))[/CODE] This is the first column in the first of 7 groups of columns. More groups will need to be added

 

[krissz]

solved it. Thanks for the help
=SUM(INDIRECT(ADDRESS(St_Row,AS6,1)):INDIRECT(ADDRESS(End_Row,AS6,1)))
Where St_Row & End_Row are the start & end rows & AS6 is the Column.

 

[jasonb75]

Indirect is not necessary for what you need, see if this works, change the bit in bold to cover all of the columns that you need to sum.

=SUMIFS(INDEX($A:$Z,0,AS6), S_Date," >="&$AG$3, S_Date,"<="&$AG$4)