sumproduct question

[Dominique Feteau]

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

 

[Bob Phillips]

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

--

HTH

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

 

[KL]

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

 

[Guest]

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

 

[Aladin Akyurek]

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))

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.

 

[Dominique Feteau]

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:E3018>38322),--(E2:E3018<38352),--(F2:F3018="POE-Cite"))

works like a charm.

thanx for the help.
Niq

 

[Bob Phillips]

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)

 

[Aladin Akyurek]

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.

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:E3018>38322),--(E2:E3018<38352),--(F2:F3018="POE-Cite"))

works like a charm.

thanx for the help.
Niq

--

[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.

 

[KL]

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