Sum between two dates

[Gusher]

In cell A1 I have a date, in cell A2 I have another later date. In column B I have dates and in column C and D I have numbers. I need a function to return the sum of the numbers in cols C and D between the dates specified in cells A1 and A2.

Many thanks

 

[Jonmo1]

Try

=SUMPRODUCT((B1:B100>=A1)*(B1:B100<=A2)*C1:D100)

 

[Gusher]

Try

=SUMPRODUCT((B1:B100>=A1)*(B1:B100<=A2)*C1:D100)

Sorry I did not mean the sum of as in the product I meant the simple addition

 

[Jonmo1]

Try it.

 

[Gusher]

Hi did not work maybe I explained it wrong.

 

[Jonmo1]

Works for me.
In what way did it not work for you?
Do you get an error? What error?
Do you get the wrong reslult? What result DO you get?

Unknown
	A	B	C	D	E	F
1	7/1/2017	8/15/2017	40	97		342
2	7/31/2017	6/2/2017	87	41
3		8/23/2017	34	70
4		7/26/2017	88	21
5		6/8/2017	61	1
6		8/4/2017	42	38
7		7/29/2017	24	98
8		7/18/2017	16	84
9		6/19/2017	62	16
10		7/29/2017	10	1
Sheet1

Cell Formulas
Range	Formula
F1	=SUMPRODUCT((B1:B100>=A1)*(B1:B100<=A2)*C1:D100)

 

[Gusher]

ok thanks I i will go over my formula

 

[Gusher]

Hi, I get 0, strange

 

[Jonmo1]

That means either none of the rows are actually between the 2 dates
Or in the rows that ARE between the 2 dates, the values in C and D are blank, or happen to add up to 0.

Most likely cause is your dates aren't really dates, either the ones in A1 & A2, and/or the ones in column B.

Test them with
=ISNUMBER(A1)

Repeat for A2, and your dates in B

Are they true or false?

 

[Gusher]

That means either none of the rows are actually between the 2 dates
Or in the rows that ARE between the 2 dates, the values in C and D are blank, or happen to add up to 0.

Most likely cause is your dates aren't really dates, either the ones in A1 & A2, and/or the ones in column B.

Test them with
=ISNUMBER(A1)

Repeat for A2, and your dates in B

Are they true or false?

Ah, you are so correct, I somehow entered the dates as a string text, works now, many thanks. Out of curiosity, I notice you have repied to over 40,000 messages. Other the thanks you get from grateful people like me, what motivates you to spend so much time solving other peoples problems. or maybe its the same as doing a crossword puzzle, fills the time and exercises the brain?