# SUMIF/SUMIFS for cell content

#### bakarken

##### New Member
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.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### BarryL

##### Well-known Member
wheres the range with the dates in the sum sheet? if there are numbers in G, where are the dates held ?

#### AlanY

##### Well-known Member
in your example, where are the corresponding dates of the values in Sheet2!G1:G10?

#### BarryL

##### Well-known Member
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

##### Well-known Member
this assume the corresponding dates are in Col F, revise to suit

Excel 2012
ABCDE
1110/12/2016Sheet4G1F1
213/12/2016G10F10
Sheet3
Cell Formulas
RangeFormula
A1=SUMIFS(INDIRECT("'"&C1&"'!"&D1&":"&D2),INDIRECT("'"&C1&"'!"&E1&":"&E2), ">="&B1,INDIRECT("'"&C1&"'!"&E1&":"&E2),"<="&B2)

#### bakarken

##### New Member
Amazing thank you both.

Replies
6
Views
179
Replies
6
Views
552
Replies
13
Views
445
Replies
0
Views
197
Replies
2
Views
592

1,195,963
Messages
6,012,589
Members
441,714
Latest member
mcgeesusana

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back