Sum values based between two dates and another varible

[slamanager]

Hello everyone,

I want to sum values in column "C" based on if they fall between and equal to two dates, dates in "A" and a varible in "B"

Column "A" a2-a32 dates of each day in Jan 09 31 days.
Column "B" b2-b32 letters either X Y Z randomly through range some cells may have a letter.
Column "C" Dollar value

in cell D1 Startdate in cell D2 Enddate
Formula located in D3 is the sum of all values falling between the date ranges and by one of the letters X. D4 Y. D5 Z.

Ive looked at sumif and sumproduct but have got really confused especially using inputs as criterias such as the dates any help would be apperciated.

 

[Peter_SSs]

I've changed the layout slightly, but see if this formula (copied down) does what you want.

Excel Workbook
	A	B	C	D	E
1	Date	Variable	Amount	Start	08/01/2009
2	01/01/2009	X	4	End	20/01/2009
3	02/01/2009	Y	3	X	71
4	03/01/2009	Z	2	Y	20
5	04/01/2009	Z	3	Z	14
6	05/01/2009	X	4
7	06/01/2009	X	43
8	07/01/2009	X	2
9	08/01/2009	Z	4
10	09/01/2009	Y	5
11	10/01/2009	Y	6
12	11/01/2009	Z	6
13	12/01/2009	X	65
14	13/01/2009	X
15	14/01/2009	Z	4
16	15/01/2009	Y	4
17	16/01/2009	Y	3
18	17/01/2009	X	3
19	18/01/2009	X	3
20	19/01/2009	Y	2
21	20/01/2009	Z
22	21/01/2009	Z	2
23	22/01/2009	X	2
24	23/01/2009	X	2
25	24/01/2009	X	2
26	25/01/2009	Z	2
27	26/01/2009	Y	2
28	27/01/2009	Y	24
29	28/01/2009	Z	4
30	29/01/2009	X	5
31	30/01/2009	X	5
32	31/01/2009	Z
SUM

 

[slamanager]

Amazingly fast response thankyou,

Yes spot on layout. But can i ask what the "--" does in the formula.

 

[Peter_SSs]

Amazingly fast response thankyou,

Yes spot on layout. But can i ask what the "--" does in the formula.

See post #2 here

 

[slamanager]

Ok ive applied it to my actual sheet the only differance is that the letter varible comes from a vlookup from a table.

This formula is to total a large data block so its not on the same sheet ive checked and all the refrences are good and the ranges are correct but im getting N/A returned.

 

[slamanager]

=SUMPRODUCT(--(DataInput!$G$3:$G$37990>=B32),--(DataInput!$G$3:$G$37990<=C32),--(DataInput!$O$3:$O$37990="F"),DataInput!$F$3:$F$37990)

G = date range
O = Vlookup for a F D or N letter
F = is the value
B32 is the start date
C32 is the end date

 

[Peter_SSs]

Seems to work for me.

Here is my small test data using a simple VLOOKUP for column O.
The results (second screen shot below) should (and does) add the blue rows.

Excel Workbook
	F	G	N	O	P	Q	R
2
3	1	01/01/2009	1	F		1	F
4	1	02/01/2009	3	N		2	D
5	1	03/01/2009	1	F		3	N
6	1	01/01/2009	1	F
7	1	02/01/2009	1	F
8	1	03/01/2009	2	D
9	1	02/01/2009	1	F
10	1	02/01/2009	3	N
11
DataInput

And my results sheet, using your formula copied directly from your post.

Excel Workbook
	B	C	D
31	Start	End	Result
32	02/01/2009	03/01/2009	3
SUMPRODUCT

 

[slamanager]

Strange its not working i'll do some changes and relocate the summary to the same sheet im really puzzled.

Just to ask this isnt a array formula that im missing is it ?

 

[Peter_SSs]

Strange its not working i'll do some changes and relocate the summary to the same sheet im really puzzled.

Just to ask this isnt a array formula that im missing is it ?

No, it is not an array formula.

Check that you don't have any #NA results in your data area. My guess is that the most likely spot would be in column O where your VLOOKUP may be trying to look up something that doesn't exist in the lookup table. Like this:

Excel Workbook
	F	G	N	O	P	Q	R
3	1	01/01/2009	1	F		1	F
4	1	02/01/2009	3	N		2	D
5	1	03/01/2009	1	F		3	N
6	1	01/01/2009	1	F
7	1	02/01/2009	1	F
8	1	03/01/2009	4	#N/A
9	1	02/01/2009	1	F
10	1	02/01/2009	3	N
DataInput

Excel Workbook
	B	C	D
31	Start	End	Result
32	02/01/2009	03/01/2009	#N/A
SUMPRODUCT

 

[slamanager]

YEs there are some N/A values in that date range for the FDN lookup i will use iserror and check if that sorts out the issue.