formula to add up sums in a date range

[MarkDave]

Hi, I have a spreadsheet where Colin e has a date and column d has a figure. I then have a table on a separate sheet which has a date range, I.e 2/10/2017 - 8/10/2017 - and then a total
cell. I need the total cell to be populated with the sum of columnd d where these figures fall within the date range - any ideas?

 

[FormR]

Hi, take a look at the SUMIFS() formula - for example:

Excel 2013/2016
	D	E	F	G	H	I
1	Figure	Date		From	To	Total
2	5	01/10/2017		02/10/2017	08/10/2017	44
3	6	02/10/2017
4	7	03/10/2017
5	8	04/10/2017
6	9	05/10/2017
7	5	06/10/2017
8	4	07/10/2017
9	5	08/10/2017
10	4	09/10/2017
11	7	10/10/2017
Sheet1

Cell Formulas
Range	Formula
I2	=SUMIFS(D:D,E:E,">="&G2,E:E,"<="&H2)

 

[MarkDave]

That works a treat, thank you - I just need to work out how to total it up on sheet two now...thanks again.

 

[FormR]

I just need to work out how to total it up on sheet two now

Hi, if you mean the "figure" and "date" columns are on sheet2 then something like:

=SUMIFS(Sheet2!D:D,Sheet2!E:E,">="&G2,Sheet2!E:E,"<="&H2)

 

[MarkDave]

Hi, if you mean the "figure" and "date" columns are on sheet2 then something like:

=SUMIFS(Sheet2!D:D,Sheet2!E:E,">="&G2,Sheet2!E:E,"<="&H2)

This is excellent thank you. It works perfectly. I may need to share this with someone who uses excel pre 2007, is there a similar sum product formula I could use?

 

[FormR]

I may need to share this with someone who uses excel pre 2007

Hi, that's an old version !

This would be equivalent:

=SUMPRODUCT(--(Sheet2!$D$1:$D$1000),--(Sheet2!$E$1:$E$1000>=G22),--(Sheet2!$E$1:$E$1000<=H2))