sumproduct question
 

i have a table that has column of dates and a column of names of forms.

heres the sumproduct function i'm using:
=SUMPRODUCT(--(Data!E$2:E$3850=A4-30),--(Data!E$2:E$3850<=A4),--(Data!F$2:F
$3850=B$3))

A4 = 12/31/04 (Date)
B3 = "POE-Cite" (Form)

only problem is that its not very accurate. instead of using "A4-30", is
there a way to use a specific range? i thought using an "AND" statement
would work, but it isnt.

thanks

Why not put the other date in A5 and test against that?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dominique Feteau" wrote in message
...
i have a table that has column of dates and a column of names of forms.

heres the sumproduct function i'm using:

=SUMPRODUCT(--(Data!E$2:E$3850=A4-30),--(Data!E$2:E$3850<=A4),--(Data!F$2:F
$3850=B$3))

A4 = 12/31/04 (Date)
B3 = "POE-Cite" (Form)

only problem is that its not very accurate. instead of using "A4-30", is
there a way to use a specific range? i thought using an "AND" statement
would work, but it isnt.

thanks

Hi Dominique,

How about this:

=SUMPRODUCT(--(TEXT(Data!E$2:E$3850,"mmyy")=TEXT(A4,"mmyy")),--(Data!F$2:F$3850=B$3))

Regard,
KL



"Dominique Feteau" wrote in message
...
i have a table that has column of dates and a column of names of forms.

heres the sumproduct function i'm using:
=SUMPRODUCT(--(Data!E$2:E$3850=A4-30),--(Data!E$2:E$3850<=A4),--(Data!F$2:F
$3850=B$3))

A4 = 12/31/04 (Date)
B3 = "POE-Cite" (Form)

only problem is that its not very accurate. instead of using "A4-30", is
there a way to use a specific range? i thought using an "AND" statement
would work, but it isnt.

thanks

I don't see anything wrong with the formula. what is the inaccuracy?

"Dominique Feteau" wrote:

i have a table that has column of dates and a column of names of forms.

heres the sumproduct function i'm using:
=SUMPRODUCT(--(Data!E$2:E$3850=A4-30),--(Data!E$2:E$3850<=A4),--(Data!F$2:F
$3850=B$3))

A4 = 12/31/04 (Date)
B3 = "POE-Cite" (Form)

only problem is that its not very accurate. instead of using "A4-30", is
there a way to use a specific range? i thought using an "AND" statement
would work, but it isnt.

thanks

Are you trying to count for November 2004? If so, try:

A4: 1-Nov-04

which is the first day date of the month/year of interest.

Now invoke:

=SUMPRODUCT(--(Data!E$2:E$3850-DAY(Data!E$2:E$3850)+1=A4),--(Data!F$2:F$3850=B$3))

Dominique Feteau wrote:
i have a table that has column of dates and a column of names of forms.

heres the sumproduct function i'm using:
=SUMPRODUCT(--(Data!E$2:E$3850=A4-30),--(Data!E$2:E$3850<=A4),--(Data!F$2:F
$3850=B$3))

A4 = 12/31/04 (Date)
B3 = "POE-Cite" (Form)

only problem is that its not very accurate. instead of using "A4-30", is
there a way to use a specific range? i thought using an "AND" statement
would work, but it isnt.

thanks



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

A collegue of mine figured it out. The reason it was inaccurate was because
I was telling it start at this date and subtract this many days to count
between. For some odd reason, a few values weren't counted. (e.g. there
were 55 "POE-Cite"'s in december, but it only counted 54. So instead of
having the formula calculate the start and end date on its own, I gave it
specific dates. Here's the solution I got:

=SUMPRODUCT(--(E2:E301838322),--(E2:E3018<38352),--(F2:F3018="POE-Cite"))

works like a charm.

thanx for the help.
Niq

"bj" wrote in message
...
I don't see anything wrong with the formula. what is the inaccuracy?

"Dominique Feteau" wrote:

i have a table that has column of dates and a column of names of forms.

heres the sumproduct function i'm using:

=SUMPRODUCT(--(Data!E$2:E$3850=A4-30),--(Data!E$2:E$3850<=A4),--(Data!F$2:F
$3850=B$3))

A4 = 12/31/04 (Date)
B3 = "POE-Cite" (Form)

only problem is that its not very accurate. instead of using "A4-30", is
there a way to use a specific range? i thought using an "AND" statement
would work, but it isnt.

thanks

More intuitive to use

=SUMPRODUCT(--(E2:E3018--"2004-12-01"),--(E2:E3018<--"2004-12-31"),--(F2:F3
018="POE-Cite"))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dominique Feteau" wrote in message
...
A collegue of mine figured it out. The reason it was inaccurate was
because
I was telling it start at this date and subtract this many days to count
between. For some odd reason, a few values weren't counted. (e.g. there
were 55 "POE-Cite"'s in december, but it only counted 54. So instead of
having the formula calculate the start and end date on its own, I gave it
specific dates. Here's the solution I got:

=SUMPRODUCT(--(E2:E301838322),--(E2:E3018<38352),--(F2:F3018="POE-Cite"))

works like a charm.

thanx for the help.
Niq

"bj" wrote in message
...
I don't see anything wrong with the formula. what is the inaccuracy?

"Dominique Feteau" wrote:

i have a table that has column of dates and a column of names of
forms.

heres the sumproduct function i'm using:


=SUMPRODUCT(--(Data!E$2:E$3850=A4-30),--(Data!E$2:E$3850<=A4),--(Data!F$2:F
$3850=B$3))

A4 = 12/31/04 (Date)
B3 = "POE-Cite" (Form)

only problem is that its not very accurate. instead of using "A4-30",
is
there a way to use a specific range? i thought using an "AND"
statement
would work, but it isnt.

thanks

What is wrong with:

=SUMPRODUCT(--(Data!E$2:E$3850-DAY(Data!E$2:E$3850)+1=A4),--(Data!F$2:F$3850=B$3))

where A4 houses: 1-Dec-04 and B3 POE-Cite?

Note that A4 contains the first day date of the month/year of interest.

Dominique Feteau wrote:
A collegue of mine figured it out. The reason it was inaccurate was because
I was telling it start at this date and subtract this many days to count
between. For some odd reason, a few values weren't counted. (e.g. there
were 55 "POE-Cite"'s in december, but it only counted 54. So instead of
having the formula calculate the start and end date on its own, I gave it
specific dates. Here's the solution I got:

=SUMPRODUCT(--(E2:E301838322),--(E2:E3018<38352),--(F2:F3018="POE-Cite"))

works like a charm.

thanx for the help.
Niq

"bj" wrote in message
...

I don't see anything wrong with the formula. what is the inaccuracy?

"Dominique Feteau" wrote:


i have a table that has column of dates and a column of names of forms.

heres the sumproduct function i'm using:


=SUMPRODUCT(--(Data!E$2:E$3850=A4-30),--(Data!E$2:E$3850<=A4),--(Data!F$2:F

$3850=B$3))

A4 = 12/31/04 (Date)
B3 = "POE-Cite" (Form)

only problem is that its not very accurate. instead of using "A4-30", is
there a way to use a specific range? i thought using an "AND" statement
would work, but it isnt.

thanks







--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Using Aladin's idea, I think you can make the formula slightly more
flexible:

=SUMPRODUCT(--(Data!$E$2:$E$3850-DAY(Data!$E$2:$E$3850)=$D$1-DAY($D$1)),--(Data!$F$2:$F$3850=$D$2))

now you can put any date into [A4]

The performance difference between the original formula and the above
version of it seems to be negligeble if copying over 800 to 5000 cells, but
you probably wouldn't want to copy it to many cells anyway :-).

Regards,
KL


"Dominique Feteau" wrote in message
...
i have a table that has column of dates and a column of names of forms.

heres the sumproduct function i'm using:
=SUMPRODUCT(--(Data!E$2:E$3850=A4-30),--(Data!E$2:E$3850<=A4),--(Data!F$2:F
$3850=B$3))

A4 = 12/31/04 (Date)
B3 = "POE-Cite" (Form)

only problem is that its not very accurate. instead of using "A4-30", is
there a way to use a specific range? i thought using an "AND" statement
would work, but it isnt.

thanks