SUMIF/SUMIFS for cell content

[bakarken]

Hi all

I am looking for an excel formula (for Excel 2010) where the user simply enters information in certain cells, and another cell completes SUMIF to calculate the sum of this criteria.

For example

A1: Contains the SUMIF formula
B1: Contains a START DATE as entered by the user (e.g. 01/01/2016)
B2: Contains an END DATE as entered by the user (e.g. 31/12/2016)
C1: Contains a SHEET NAME as entered by the user (e.g. Sheet2)
D1: Contains a START CELL REFERENCE as entered by the user for a sum range (e.g. G1)
D2: Contains an END CELL REFERENCE as entered by the user for a sum range (e.g. G10)

I am struggling to combine the SUMIF/SUMIFS and INDIRECT formulas, so perhaps someone could help.

In cell A1, I want the formula to display the SUM of all values, that are between the two dates specified (in B1/B2), for a sum range between two cells specified (in cells D1/D2 in sheet C1)

So in my examples, A1 would show the sum of all values in Sheet2!G1:G10, if they are between 01/01/2016 and 31/12/2016 (but this would vary depending on the users choices)

Any help would be appreciated.

 

[BarryL]

wheres the range with the dates in the sum sheet? if there are numbers in G, where are the dates held ?

 

[AlanY]

in your example, where are the corresponding dates of the values in Sheet2!G1:G10?

 

[BarryL]

If they were in H the formula would look like the below

=SUMIFS(INDIRECT("'"&C1&"'!"&D1&":"&E1),INDIRECT("'"&C1&"'!H1:H10"),">="&B1,INDIRECT("'"&C1&"'!H1:H10"),"<="&B2)

 

[AlanY]

this assume the corresponding dates are in Col F, revise to suit

Excel 2012
	A	B	C	D	E
1	1	10/12/2016	Sheet4	G1	F1
2		13/12/2016		G10	F10
Sheet3

Cell Formulas
Range	Formula
A1	=SUMIFS(INDIRECT("'"&C1&"'!"&D1&":"&D2),INDIRECT("'"&C1&"'!"&E1&":"&E2), ">="&B1,INDIRECT("'"&C1&"'!"&E1&":"&E2),"<="&B2)

 

[bakarken]

Amazing thank you both.