Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Ecell 2000 SP3
I have a user that is using the following function:
=SUMPRODUCT(--(MONTH(Index!J3:J9997)=1),--(YEAR(Index!J3:J9997)=1998)) The cells in question in the index sheet are formated for date ex. 8/29/2005. Is this the correct function for this, if yes what is wrong, if no what function should i have my user use? Thanks much |
#2
|
|||
|
|||
It seems like a very nice formula if your user is counting the number of dates
in January of 1998. Another formula that's just as nice: =sumproduct(--(text(index!j3:j9997,"yyyymm")="199801") holy dog wrote: I have a user that is using the following function: =SUMPRODUCT(--(MONTH(Index!J3:J9997)=1),--(YEAR(Index!J3:J9997)=1998)) The cells in question in the index sheet are formated for date ex. 8/29/2005. Is this the correct function for this, if yes what is wrong, if no what function should i have my user use? Thanks much -- Dave Peterson |
#3
|
|||
|
|||
Yes she is, but she gets #VALUE! for the result. Thanks much for your
assistance. "Dave Peterson" wrote: It seems like a very nice formula if your user is counting the number of dates in January of 1998. Another formula that's just as nice: =sumproduct(--(text(index!j3:j9997,"yyyymm")="199801") holy dog wrote: I have a user that is using the following function: =SUMPRODUCT(--(MONTH(Index!J3:J9997)=1),--(YEAR(Index!J3:J9997)=1998)) The cells in question in the index sheet are formated for date ex. 8/29/2005. Is this the correct function for this, if yes what is wrong, if no what function should i have my user use? Thanks much -- Dave Peterson |
#4
|
|||
|
|||
Your formula is working nice. Thanks much. I learned something today.
"Dave Peterson" wrote: It seems like a very nice formula if your user is counting the number of dates in January of 1998. Another formula that's just as nice: =sumproduct(--(text(index!j3:j9997,"yyyymm")="199801") holy dog wrote: I have a user that is using the following function: =SUMPRODUCT(--(MONTH(Index!J3:J9997)=1),--(YEAR(Index!J3:J9997)=1998)) The cells in question in the index sheet are formated for date ex. 8/29/2005. Is this the correct function for this, if yes what is wrong, if no what function should i have my user use? Thanks much -- Dave Peterson |
#5
|
|||
|
|||
That fourmula worked and I was able to modify it for the diferent months and
years, plus it was esier to understand. Would you be able to help me with this formula and also let me know what the nestes =isnumber means Thanks much: =SUMPRODUCT(--(Index!$L$3:$L$9997="home"),--(ISNUMBER(Index!$J$3:$J$9997)),--(MONTH(Index!$J$3:$J$9997)=1),--(YEAR(Index!$J$3:$J$9997)=2005)) "Dave Peterson" wrote: It seems like a very nice formula if your user is counting the number of dates in January of 1998. Another formula that's just as nice: =sumproduct(--(text(index!j3:j9997,"yyyymm")="199801") holy dog wrote: I have a user that is using the following function: =SUMPRODUCT(--(MONTH(Index!J3:J9997)=1),--(YEAR(Index!J3:J9997)=1998)) The cells in question in the index sheet are formated for date ex. 8/29/2005. Is this the correct function for this, if yes what is wrong, if no what function should i have my user use? Thanks much -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
office 2000 | Excel Discussion (Misc queries) | |||
Hyperlink in Excel 2000 can't open bookmarked Word 2000 file | Excel Discussion (Misc queries) | |||
moving administrative installation of MS Office 2000 | Excel Discussion (Misc queries) | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |