|
SUMPRODUCT COUNT DATE
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 |
SUMPRODUCT COUNT DATE
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 |
SUMPRODUCT COUNT DATE
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 |
SUMPRODUCT COUNT DATE
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 |
SUMPRODUCT COUNT DATE
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 |
SUMPRODUCT COUNT DATE
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 |
SUMPRODUCT COUNT DATE
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 |
SUMPRODUCT COUNT DATE
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 |
SUMPRODUCT COUNT DATE
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? Bob Phillips wrote: 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] Thanks for all your help -- Message posted via http://www.officekb.com |
| All times are GMT +1. The time now is 12:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com