Sumif with AND statements

[Cdworks]

Good day!

I am somewhat new to excel i have the basic excel skills and am working on an excel file to produce a production calendar from a quickbooks export.

I am needing to do a sumif statement for a range between two dates.

sum if ( X:Z, Date1>=date2 and <= date3, Sum range)

Could anyone help me with this?

TIA
- CDWorks

 

[Scott Huish]

X:Z contain your dates?

If so:
(You didn't specify your sum range, I used AA)

=SUMPRODUCT(--(X1:X1000>=Y1:Y1000),--(Y1:Y1000<=Z1:Z1000),AA1:AA1000)

 

[T. Valko]

Good day!

I am somewhat new to excel i have the basic excel skills and am working on an excel file to produce a production calendar from a quickbooks export.

I am needing to do a sumif statement for a range between two dates.

sum if ( X:Z, Date1>=date2 and <= date3, Sum range)

Could anyone help me with this?

TIA
- CDWorks

What version of Excel are you using?

 

[Cdworks]

I have a list with dates in one row, the two dates for the range i want info on, and a row for quantities.

I want to say" the sum of quantities between the two dates by reading off of the given dates"

I used the formula you gave me and its returning #NAME, i am going to check some formatting.

 

[Cdworks]

excel version 2003

 

[T. Valko]

excel version 2003

Try this...

Book1
	A	B	C	D	E	F
2	4/20/2007	9/21/2013	53	_	1/1/2010	6/1/2011
3	3/6/2010	9/12/2010	35	_	_	_
4	5/6/2002	12/5/2006	100	_	160	_
5	1/27/2010	2/11/2011	95	_	_	_
6	8/11/2005	10/27/2007	57	_	_	_
7	12/8/2006	7/14/2008	80	_	_	_
8	6/1/2003	8/30/2008	31	_	_	_
9	6/24/2003	9/21/2014	95	_	_	_
10	2/4/2010	2/11/2011	30	_	_	_
Sheet1

Formula entered in E4:

=SUMPRODUCT(--(A2:A10>=E2),--(B2:B10<=F2),C2:C10)

 

[Cdworks]

I have that but i only have one Date (A) not two (A & B). I need to know if this "Due date"(A) falls between this range ( your E & F).

Also is there any way to pull the info for information on the same row as the dates that fall withing the range to create an alternate spread sheet with info just within that relevant range.

Thank you all for the help!

 

[T. Valko]

I have that but i only have one Date (A) not two (A & B). I need to know if this "Due date"(A) falls between this range ( your E & F).

Also is there any way to pull the info for information on the same row as the dates that fall withing the range to create an alternate spread sheet with info just within that relevant range.

Thank you all for the help!

OK, I get it!

Book1
	A	B	C	D	E
2	12/29/2010	53	_	1/1/2011	1/5/2011
3	12/30/2010	35	_	_	_
4	12/31/2010	100	_	263	_
5	1/1/2011	95	_	_	_
6	1/2/2011	57	_	_	_
7	1/3/2011	80	_	_	_
8	1/4/2011	31	_	_	_
9	1/7/2011	95	_	_	_
10	1/8/2011	30	_	_	_
Sheet1

Try one of these formulas:

=SUMIF(A2:A10,">="&D2,B2:B10)-SUMIF(A2:A10,">"&E2,B2:B10)

=SUMPRODUCT(--(A2:A10>=D2),--(A2:A10<=E2),B2:B10)

For your second question...let's get this sum working first then we'll tackle that other problem.

 

[Cdworks]

I believe i may be making this more complicated than it is.

 

[T. Valko]

I believe i may be making this more complicated than it is.

Not a problem!