SUMIF formula if another field falls between a date range

[benchris]

What I am trying to do is find total dollars sold by a certain value, but the date value of another field has to be less then or equal to a date.

I have a table where a sumif is working pulling in the sum of certain values:

The formula is: =SUMIF(H:H,Z7,P:P)
H:H = the column that has all of the item group's
Z7 = item group number
P:P is the column of the value to be returned

There is also another field that shows the date the transaction took place. I am looking to create a formula that would state if the date range is less than or equal to <this field>, then the sumif statement would give back the results where the date column is equal or less than the date in the field.

For example

Col A Col B Col C
Date Item Group Value
02/04/2016 115 $1999
02/23/2015 116 $100
01/31/2016 115 $500

Field AA7 =SUMIF(B:B,Z7,C:C) and Z7 has value 115 in it and it would return $2449
Field AA8 = =SUMIF(B:B,Z8,C:C) and Z8 has value 116 in it and it would return $100

What I am trying to figure out is how to have a static date field and then a formula that would say that if the date is "<=" than the date stated in field Z10 then include the results from that row in the results.

Does this make sense? Any ideas on how I can accomplish this?

I appreciate the help. I have been reading here a lot, but finally registered today.

Thanks
Ben

 

[FDibbins]

maybe this (untested)...
=SUMIF(P:P,H:H,Z7,A:A,">="cell-with-start-date",A:A,"<="cell-with-end-date"

 

[benchris]

It is stating I have entered too many arguments for this function.

Is there a way that a formula can lookup by dates logically in a column and know numerically if the date is less than or equal to the one it is looking up against? Or does the date have to be in a range?

 

[FDibbins]

grrrr HUGE apologies, I left out a letter...
=SUMIFS(P:P,H:H,Z7,A:A,">="cell-with-start-date",A:A,"<="cell-with-end-date")

 

[benchris]

Thanks. When I put the date fields in that have the start date and the end date I had to write the formula like this to get it to show up as a formula, but then I got zero results

=SUMIFS(P:P,H:H,Z7,F:F,">=" &AB41,F:F,"<=" &AA41)

Column F is the actual column of where the dates are held

 

[FDibbins]

Are your dates real dates?

Test with =ISNUMBER(cell-ref) FALSE indicates they are text, not dates

 

[benchris]

=ISNUMBER(F2) = True

 

[FDibbins]

See if this helps...

	A​	B​	C​	D​	E​
1​	Code	Date	Value		aa
2​	aa	2/1/2016​	1​	Start	2/3/2016​
3​	bb	2/2/2016​	2​	End	2/10/2016​
4​	cc	2/3/2016​	3​	Amount	21​
5​	aa	2/4/2016​	4​
6​	bb	2/5/2016​	5​
7​	cc	2/6/2016​	6​
8​	aa	2/7/2016​	7​
9​	bb	2/8/2016​	8​
10​	cc	2/9/2016​	9​
11​	aa	2/10/2016​	10​
12​	bb	2/11/2016​	11​
13​	cc	2/12/2016​	12​
14​	aa	2/13/2016​	13​
15​	bb	2/14/2016​	14​
16​	cc	2/15/2016​	15​
17​	aa	2/16/2016​	16​
18​	bb	2/17/2016​	17​
19​	cc	2/18/2016​	18​
20​	aa	2/19/2016​	19​
21​	bb	2/20/2016​	20​
22​	cc	2/21/2016​	21​

E4=SUMIFS(C:C,A:A,E1,B:B,">="&E2,B:B,"<="&E3)

 

[benchris]

Do the values in the date column have to be in descending order, or can they be mixed up?

 

[FDibbins]

Should not matter if they are mixed

	A​	B​	C​	D​	E​
1​	Code	Date	Value		aa
2​	aa	2/1/2016​	1​	Start	2/3/2016​
3​	bb	2/3/2016​	2​	End	2/10/2016​
4​	cc	2/15/2016​	3​	Amount	21​
5​	aa	2/4/2016​	4​
6​	bb	2/5/2016​	5​
7​	cc	2/6/2016​	6​
8​	aa	2/7/2016​	7​
9​	bb	2/8/2016​	8​
10​	cc	2/9/2016​	9​
11​	aa	2/10/2016​	10​
12​	bb	2/11/2016​	11​
13​	cc	2/12/2016​	12​
14​	aa	2/13/2016​	13​
15​	bb	2/14/2016​	14​
16​	cc	2/2/2016​	15​
17​	aa	2/16/2016​	16​