I don't know what I'm doing wrong, but if I use this formula I get a count of
14 instead of 5, which I what I count when I set the filters. I can't figure
out where the 14 comes from. There is only 5 dates less that today's date in
column AI. I have been working on this darn thing for 2 days and just can't
figure it out.


=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),--
(Odyssey!$AI$2:$AI$771<=TODAY()))

If i change the formula to this

=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),--(MONTH
(Odyssey!$AI$2:$AI$771)=8))

I get the right count of 5

HELP

Basically all I want to do is count the dates in the PLAN Date column (AI)
that equal to a condition or Y in column (AH)
Compare it to the Actual date(AJ)

I am bald head right now, lol

Thanks for all your help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

Do you have any blanks in AI2:AI771 as this will pass that test?

If so, try

=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),
--(ISNUMBER(Odyssey!$AI$2:$AI$771)),--(Odyssey!$AI$2:$AI$771<=TODAY()))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"s2m via OfficeKB.com" <u23063@uwe wrote in message
news:64ab2e85da8cd@uwe...
I don't know what I'm doing wrong, but if I use this formula I get a count
of
14 instead of 5, which I what I count when I set the filters. I can't
figure
out where the 14 comes from. There is only 5 dates less that today's date
in
column AI. I have been working on this darn thing for 2 days and just
can't
figure it out.


=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),--
(Odyssey!$AI$2:$AI$771<=TODAY()))

If i change the formula to this


=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),--(MONT
H
(Odyssey!$AI$2:$AI$771)=8))

I get the right count of 5

HELP

Basically all I want to do is count the dates in the PLAN Date column (AI)
that equal to a condition or Y in column (AH)
Compare it to the Actual date(AJ)

I am bald head right now, lol

Thanks for all your help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

Yes I have blanks in AI. I will try your formula.

I did think of something, does the order of the array make a difference?



Bob Phillips wrote:
Do you have any blanks in AI2:AI771 as this will pass that test?

If so, try

=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),
--(ISNUMBER(Odyssey!$AI$2:$AI$771)),--(Odyssey!$AI$2:$AI$771<=TODAY()))

I don't know what I'm doing wrong, but if I use this formula I get a count of
14 instead of 5, which I what I count when I set the filters. I can't figure
[quoted text clipped - 6 lines]

If i change the formula to this

=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),--(MONT
H
(Odyssey!$AI$2:$AI$771)=8))

[quoted text clipped - 9 lines]

Thanks for all your help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

the ISNUMBER works!

Thank you guys so much





Bob Phillips wrote:
Do you have any blanks in AI2:AI771 as this will pass that test?

If so, try

=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),
--(ISNUMBER(Odyssey!$AI$2:$AI$771)),--(Odyssey!$AI$2:$AI$771<=TODAY()))

I don't know what I'm doing wrong, but if I use this formula I get a count of
14 instead of 5, which I what I count when I set the filters. I can't figure
[quoted text clipped - 6 lines]

If i change the formula to this

=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),--(MONT
H
(Odyssey!$AI$2:$AI$771)=8))

[quoted text clipped - 9 lines]

Thanks for all your help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

Glad you got it to work. As to your question, no the order does not make a
difference.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"s2m via OfficeKB.com" <u23063@uwe wrote in message
news:64ce8e296433a@uwe...
the ISNUMBER works!

Thank you guys so much





Bob Phillips wrote:
Do you have any blanks in AI2:AI771 as this will pass that test?

If so, try

=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),
--(ISNUMBER(Odyssey!$AI$2:$AI$771)),--(Odyssey!$AI$2:$AI$771<=TODAY()))

I don't know what I'm doing wrong, but if I use this formula I get a
count of
14 instead of 5, which I what I count when I set the filters. I can't
figure
[quoted text clipped - 6 lines]

If i change the formula to this


=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),--(MON
T
H
(Odyssey!$AI$2:$AI$771)=8))

[quoted text clipped - 9 lines]

Thanks for all your help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

Another question

How do you take in account for plan dates in another month being completed?

Example =SUMPRODUCT(--(Odyssey!$E$2:$E$771=B8),--(Odyssey!$AH$2:$AH$771="Y"),-
-(ISNUMBER(Odyssey!$AI$2:$AI$771)),--(Odyssey!$AI$2:$AI$771<=TODAY()))

I use this formula to count all the plan dates until 8/15/06

Then in the actual formula I use this formula. Problem is, the count I get
is always off because this counts the Actual dates for October plan dates.

=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),--
(ISNUMBER(Odyssey!$AJ$2:$AJ$771)),--(Odyssey!$AJ$2:$AJ$771<=TODAY()))

Any suggestions?

Bob Phillips wrote:
Glad you got it to work. As to your question, no the order does not make a
difference.

the ISNUMBER works!

[quoted text clipped - 20 lines]

Thanks for all your help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

Enlighten me on this. What is B7, what is B8, and where does October come
into it? As you may guess, I don't understand the problem.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"s2m via OfficeKB.com" <u23063@uwe wrote in message
news:64cfcace9cb68@uwe...
Another question

How do you take in account for plan dates in another month being
completed?

Example
=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B8),--(Odyssey!$AH$2:$AH$771="Y"),-
-(ISNUMBER(Odyssey!$AI$2:$AI$771)),--(Odyssey!$AI$2:$AI$771<=TODAY()))

I use this formula to count all the plan dates until 8/15/06

Then in the actual formula I use this formula. Problem is, the count I
get
is always off because this counts the Actual dates for October plan dates.

=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),--
(ISNUMBER(Odyssey!$AJ$2:$AJ$771)),--(Odyssey!$AJ$2:$AJ$771<=TODAY()))

Any suggestions?

Bob Phillips wrote:
Glad you got it to work. As to your question, no the order does not make
a
difference.

the ISNUMBER works!

[quoted text clipped - 20 lines]

Thanks for all your help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1

Re-check your data/filters: I tried your (first) formula and it worked OK for
me, as did the second, producing identical results if all dates were in
August.

"s2m via OfficeKB.com" wrote:

I don't know what I'm doing wrong, but if I use this formula I get a count of
14 instead of 5, which I what I count when I set the filters. I can't figure
out where the 14 comes from. There is only 5 dates less that today's date in
column AI. I have been working on this darn thing for 2 days and just can't
figure it out.


=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),--
(Odyssey!$AI$2:$AI$771<=TODAY()))

If i change the formula to this

=SUMPRODUCT(--(Odyssey!$E$2:$E$771=B7),--(Odyssey!$AH$2:$AH$771="Y"),--(MONTH
(Odyssey!$AI$2:$AI$771)=8))

I get the right count of 5

HELP

Basically all I want to do is count the dates in the PLAN Date column (AI)
that equal to a condition or Y in column (AH)
Compare it to the Actual date(AJ)

I am bald head right now, lol

Thanks for all your help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1