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

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

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

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

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