date date function
 

hi, i'm trying to look in column A for a date that falls within a specific
time frame, then look in column B to see if that date is than the date in
column A, if it is, then add the qty in column C.
ex:
A1: 5/22/08 B1: 5/23/08 C1: 4
A2: 5/23/08 B2: 5/23/08 C2: 2

so if formula is look in col A for all dates within 5/19 and 5/22, then i
want the result to show 4.
tia

date date function
 

I'm sure I've seen your post before, but try this approach - put your
start-period date in D1, your end-period date in E1 and this formula
in F1:

=SUMPRODUCT((A$1:A$100=D$1)*(A$1:A$100=E$1)*(B$1 :B$100A$1:A$100)*(C
$1:C$100))

This checks to see if the dates in A fall within the start-period and
end-period, and that the date in B is larger than A, and adds column C
if those conditions are met. I've assumed you have 100 rows, so change
this if you have more.

Hope this helps.

Pete

On May 29, 2:53*pm, Ann wrote:
hi, i'm trying to look in column A for a date that falls within a specific
time frame, then look in column B to see if that date is than the date in
column A, if it is, then add the qty in column C.
ex:
A1: 5/22/08 *B1: 5/23/08 *C1: 4
A2: 5/23/08 *B2: 5/23/08 *C2: 2

so if formula is look in col A for all dates within 5/19 and 5/22, then i
want the result to show 4.
tia

date date function
 

i've done a variation of this. i actually need to add in a value and can't
get the syntax correct...

=SUMPRODUCT(--(H3:H200="ABC")+(H3:H200="ABCD"))*(C$3:C$100DATE( 2008,5,18))*(C$3:C$100<=DATE(2008,5,22))*(D$3:D$10 0C$3:C$100)*(E$3:E$100)

i forgot, i need to base this calculation based on a field in column H.
thanks again.

date date function
 

Did you mean <=E$1 rather than =E$1 ?
--
David Biddulph

"Pete_UK" wrote in message
...
I'm sure I've seen your post before, but try this approach - put your
start-period date in D1, your end-period date in E1 and this formula
in F1:

=SUMPRODUCT((A$1:A$100=D$1)*(A$1:A$100=E$1)*(B$1 :B$100A$1:A$100)*(C
$1:C$100))

This checks to see if the dates in A fall within the start-period and
end-period, and that the date in B is larger than A, and adds column C
if those conditions are met. I've assumed you have 100 rows, so change
this if you have more.

Hope this helps.

Pete

On May 29, 2:53 pm, Ann wrote:
hi, i'm trying to look in column A for a date that falls within a specific
time frame, then look in column B to see if that date is than the date
in
column A, if it is, then add the qty in column C.
ex:
A1: 5/22/08 B1: 5/23/08 C1: 4
A2: 5/23/08 B2: 5/23/08 C2: 2

so if formula is look in col A for all dates within 5/19 and 5/22, then i
want the result to show 4.
tia

date date function
 

Yeah, you're right, David - my mistake, though the OP seems to have
got her answer from another post.

Pete

On May 29, 5:09*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
Did you mean <=E$1 rather than =E$1 ?
--
David Biddulph

"Pete_UK" wrote in message

...
I'm sure I've seen your post before, but try this approach - put your
start-period date in D1, your end-period date in E1 and this formula
in F1:

=SUMPRODUCT((A$1:A$100=D$1)*(A$1:A$100=E$1)*(B$1 :B$100A$1:A$100)*(C
$1:C$100))

This checks to see if the dates in A fall within the start-period and
end-period, and that the date in B is larger than A, and adds column C
if those conditions are met. I've assumed you have 100 rows, so change
this if you have more.

Hope this helps.

Pete

On May 29, 2:53 pm, Ann wrote:



hi, i'm trying to look in column A for a date that falls within a specific
time frame, then look in column B to see if that date is than the date
in
column A, if it is, then add the qty in column C.
ex:
A1: 5/22/08 B1: 5/23/08 C1: 4
A2: 5/23/08 B2: 5/23/08 C2: 2

so if formula is look in col A for all dates within 5/19 and 5/22, then i
want the result to show 4.
tia- Hide quoted text -

- Show quoted text -

date date function
 

Well, you've introduced things in columns D and E as well as H, so I'm
not really sure what you are doing now. However, if you use the +
symbol in this type of formula it will be taken as OR. So if your
latest condition is for column C to be ABCD or ABC then you have
missed a bracket after the -- as well as from the end of the formula.
Also, your ranges were different for column H. I think it should be:

=SUMPRODUCT(((H$3:H$100="ABC")+(H$3:H$100="ABCD")) *(C$3:C
$100DATE(2008,5,18))**(C$3:C$100<=DATE(2008,5,22) )*(D$3:D$100C$3:C
$100)*(E$3:E$100))

Hope this helps.

Pete

On May 29, 4:33*pm, Ann wrote:
i've done a variation of this. *i actually need to add in a value and can't
get the syntax correct...

=SUMPRODUCT(--(H3:H200="ABC")+(H3:H200="ABCD"))*(C$3:C$100DATE( 2008,5,18))**(C$3:C$100<=DATE(2008,5,22))*(D$3:D$1 00C$3:C$100)*(E$3:E$100)

i forgot, i need to base this calculation based on a field in column H. *
thanks again.

date date function
 

thanks guys, i've gotten the correct formula based on the below. now, here's
a modification: when i add another variable in the beginning, i can't get
the correct syntax...

=SUMPRODUCT(--(H3:H200="ABC")+(H3:H200="ABCD"))*(C$3:C$100DATE( 2008,5,18))*(C$3:C$100<=DATE(2008,5,22))*(D$3:D$10 0C$3:C$100)*(E$3:E$100)

slight modification, i want the sum the qty of what's in those rows based on
the dates.





"Pete_UK" wrote:

Yeah, you're right, David - my mistake, though the OP seems to have
got her answer from another post.

Pete

On May 29, 5:09 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
Did you mean <=E$1 rather than =E$1 ?
--
David Biddulph

"Pete_UK" wrote in message

...
I'm sure I've seen your post before, but try this approach - put your
start-period date in D1, your end-period date in E1 and this formula
in F1:

=SUMPRODUCT((A$1:A$100=D$1)*(A$1:A$100=E$1)*(B$1 :B$100A$1:A$100)*(C
$1:C$100))

This checks to see if the dates in A fall within the start-period and
end-period, and that the date in B is larger than A, and adds column C
if those conditions are met. I've assumed you have 100 rows, so change
this if you have more.

Hope this helps.

Pete

On May 29, 2:53 pm, Ann wrote:



hi, i'm trying to look in column A for a date that falls within a specific
time frame, then look in column B to see if that date is than the date
in
column A, if it is, then add the qty in column C.
ex:
A1: 5/22/08 B1: 5/23/08 C1: 4
A2: 5/23/08 B2: 5/23/08 C2: 2

so if formula is look in col A for all dates within 5/19 and 5/22, then i
want the result to show 4.
tia- Hide quoted text -

- Show quoted text -

date date function
 

Well why don't you start by looking at your parentheses and ensure that
you've got matching pairs in appropriate places for the syntax of the
SUMPRODUCT function?
--
David Biddulph

"Ann" wrote in message
...
thanks guys, i've gotten the correct formula based on the below. now,
here's
a modification: when i add another variable in the beginning, i can't get
the correct syntax...

=SUMPRODUCT(--(H3:H200="ABC")+(H3:H200="ABCD"))*(C$3:C$100DATE( 2008,5,18))*(C$3:C$100<=DATE(2008,5,22))*(D$3:D$10 0C$3:C$100)*(E$3:E$100)

slight modification, i want the sum the qty of what's in those rows based
on
the dates.





"Pete_UK" wrote:

Yeah, you're right, David - my mistake, though the OP seems to have
got her answer from another post.

Pete

On May 29, 5:09 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
Did you mean <=E$1 rather than =E$1 ?
--
David Biddulph

"Pete_UK" wrote in message

...
I'm sure I've seen your post before, but try this approach - put your
start-period date in D1, your end-period date in E1 and this formula
in F1:

=SUMPRODUCT((A$1:A$100=D$1)*(A$1:A$100=E$1)*(B$1 :B$100A$1:A$100)*(C
$1:C$100))

This checks to see if the dates in A fall within the start-period and
end-period, and that the date in B is larger than A, and adds column C
if those conditions are met. I've assumed you have 100 rows, so change
this if you have more.

Hope this helps.

Pete

On May 29, 2:53 pm, Ann wrote:



hi, i'm trying to look in column A for a date that falls within a
specific
time frame, then look in column B to see if that date is than the
date
in
column A, if it is, then add the qty in column C.
ex:
A1: 5/22/08 B1: 5/23/08 C1: 4
A2: 5/23/08 B2: 5/23/08 C2: 2

so if formula is look in col A for all dates within 5/19 and 5/22,
then i
want the result to show 4.
tia- Hide quoted text -

- Show quoted text -

date date function
 

See my reply to your earlier request on this in this same thread -
does that do it for you?

Pete

On May 29, 5:51*pm, Ann wrote:
thanks guys, i've gotten the correct formula based on the below. *now, here's
a modification: *when i add another variable in the beginning, i can't get
the correct syntax...

=SUMPRODUCT(--(H3:H200="ABC")+(H3:H200="ABCD"))*(C$3:C$100DATE( 2008,5,18))**(C$3:C$100<=DATE(2008,5,22))*(D$3:D$1 00C$3:C$100)*(E$3:E$100)

slight modification, i want the sum the qty of what's in those rows based on
the dates.

date date function
 

thanks so much guys!!

"David Biddulph" wrote:

Well why don't you start by looking at your parentheses and ensure that
you've got matching pairs in appropriate places for the syntax of the
SUMPRODUCT function?
--
David Biddulph

"Ann" wrote in message
...
thanks guys, i've gotten the correct formula based on the below. now,
here's
a modification: when i add another variable in the beginning, i can't get
the correct syntax...

=SUMPRODUCT(--(H3:H200="ABC")+(H3:H200="ABCD"))*(C$3:C$100DATE( 2008,5,18))*(C$3:C$100<=DATE(2008,5,22))*(D$3:D$10 0C$3:C$100)*(E$3:E$100)

slight modification, i want the sum the qty of what's in those rows based
on
the dates.





"Pete_UK" wrote:

Yeah, you're right, David - my mistake, though the OP seems to have
got her answer from another post.

Pete

On May 29, 5:09 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
Did you mean <=E$1 rather than =E$1 ?
--
David Biddulph

"Pete_UK" wrote in message

...
I'm sure I've seen your post before, but try this approach - put your
start-period date in D1, your end-period date in E1 and this formula
in F1:

=SUMPRODUCT((A$1:A$100=D$1)*(A$1:A$100=E$1)*(B$1 :B$100A$1:A$100)*(C
$1:C$100))

This checks to see if the dates in A fall within the start-period and
end-period, and that the date in B is larger than A, and adds column C
if those conditions are met. I've assumed you have 100 rows, so change
this if you have more.

Hope this helps.

Pete

On May 29, 2:53 pm, Ann wrote:



hi, i'm trying to look in column A for a date that falls within a
specific
time frame, then look in column B to see if that date is than the
date
in
column A, if it is, then add the qty in column C.
ex:
A1: 5/22/08 B1: 5/23/08 C1: 4
A2: 5/23/08 B2: 5/23/08 C2: 2

so if formula is look in col A for all dates within 5/19 and 5/22,
then i
want the result to show 4.
tia- Hide quoted text -

- Show quoted text -