SUMPRODUCT COUNT DATE

[s2m via OfficeKB.com]

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

 

[Bob Phillips]

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)

 

[Toppers]

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]

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?

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

 

[s2m via OfficeKB.com]

the ISNUMBER works!

Thank you guys so much

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

 

[Bob Phillips]

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)

the ISNUMBER works!

Thank you guys so much

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

 

[s2m via OfficeKB.com]

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?

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]

 

[Bob Phillips]

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)

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?

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

 

[s2m via OfficeKB.com]

Ok let me see if can explain.

I have a Exit Date Plan column (S)
I want to compare to Exit Date Actual (T)

I use sumproduct to count all the Exit Date Plan up until today count 15
Then I count all the Exit Date Actual, count 22.

The reason I have more actual dates is because they have Exited early.
Example: I have Actual Exit Date for October.

Does that make any sense?

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.

Another question

[quoted text clipped - 21 lines]