Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default count month when date is in day/month/year format

hello,
i need to count the number of times each of 5 reps made an entry in March.
the entry date is full date format (3-Mar-09) & the countif function isn't
workng.
example:
Rep Date Appt
Joe 3-mar-09 yes
Mary 5-mar-09 yes
etc

so, need to know how many times Joe made an entry in March, and need to know
how many appts were "yes" or made.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default count month when date is in day/month/year format

Try this:

=SUMPRODUCT(--(A1:A100="Joe"),--(MONTH(B1:B100)=3),--(C1:C100="yes"))

HTH
Elkar


"ccKennedy" wrote:

hello,
i need to count the number of times each of 5 reps made an entry in March.
the entry date is full date format (3-Mar-09) & the countif function isn't
workng.
example:
Rep Date Appt
Joe 3-mar-09 yes
Mary 5-mar-09 yes
etc

so, need to know how many times Joe made an entry in March, and need to know
how many appts were "yes" or made.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,389
Default count month when date is in day/month/year format

What formula did you use? Countif will do what when used properly.

When you post back, be sure to identify whether you have a true Excel date,
or whether it's text.

Regards,
Fred.

"ccKennedy" wrote in message
...
hello,
i need to count the number of times each of 5 reps made an entry in March.
the entry date is full date format (3-Mar-09) & the countif function isn't
workng.
example:
Rep Date Appt
Joe 3-mar-09 yes
Mary 5-mar-09 yes
etc

so, need to know how many times Joe made an entry in March, and need to
know
how many appts were "yes" or made.


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default count month when date is in day/month/year format

thank you, it's returning a "#VALUE!"
the date is in "date" format. will that still equate March to "3"?


"Elkar" wrote:

Try this:

=SUMPRODUCT(--(A1:A100="Joe"),--(MONTH(B1:B100)=3),--(C1:C100="yes"))

HTH
Elkar


"ccKennedy" wrote:

hello,
i need to count the number of times each of 5 reps made an entry in March.
the entry date is full date format (3-Mar-09) & the countif function isn't
workng.
example:
Rep Date Appt
Joe 3-mar-09 yes
Mary 5-mar-09 yes
etc

so, need to know how many times Joe made an entry in March, and need to know
how many appts were "yes" or made.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default count month when date is in day/month/year format

tried countif and tried sumproduct - it's not reading the date, which is in
date format in excel, properly...


"Fred Smith" wrote:

What formula did you use? Countif will do what when used properly.

When you post back, be sure to identify whether you have a true Excel date,
or whether it's text.

Regards,
Fred.

"ccKennedy" wrote in message
...
hello,
i need to count the number of times each of 5 reps made an entry in March.
the entry date is full date format (3-Mar-09) & the countif function isn't
workng.
example:
Rep Date Appt
Joe 3-mar-09 yes
Mary 5-mar-09 yes
etc

so, need to know how many times Joe made an entry in March, and need to
know
how many appts were "yes" or made.





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default count month when date is in day/month/year format

Hi,

Use the ISNUMBER() function on the date column. Do they evaluate to TRUE

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"ccKennedy" wrote in message
...
hello,
i need to count the number of times each of 5 reps made an entry in March.
the entry date is full date format (3-Mar-09) & the countif function isn't
workng.
example:
Rep Date Appt
Joe 3-mar-09 yes
Mary 5-mar-09 yes
etc

so, need to know how many times Joe made an entry in March, and need to
know
how many appts were "yes" or made.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,389
Default count month when date is in day/month/year format

Pretty tough to tell you what is going wrong until you post the formulas you
used.

Regards,
Fred.

"ccKennedy" wrote in message
...
tried countif and tried sumproduct - it's not reading the date, which is
in
date format in excel, properly...


"Fred Smith" wrote:

What formula did you use? Countif will do what when used properly.

When you post back, be sure to identify whether you have a true Excel
date,
or whether it's text.

Regards,
Fred.

"ccKennedy" wrote in message
...
hello,
i need to count the number of times each of 5 reps made an entry in
March.
the entry date is full date format (3-Mar-09) & the countif function
isn't
workng.
example:
Rep Date Appt
Joe 3-mar-09 yes
Mary 5-mar-09 yes
etc

so, need to know how many times Joe made an entry in March, and need to
know
how many appts were "yes" or made.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Tell me which "season" (Month/Day through Month/Day) a date(Month/Day/Year) falls in (any year)??? misscrf Excel Discussion (Misc queries) 1 December 14th 07 03:59 PM
date format only showing month year nikkiabm Excel Discussion (Misc queries) 4 April 30th 07 08:52 PM
Format date with just month and year Jay3253 Excel Discussion (Misc queries) 6 February 2nd 06 08:17 PM
I need date format to be set up as month/year. Aaron New Users to Excel 2 December 22nd 05 02:46 PM
Month Year Date Format Jamie Excel Worksheet Functions 2 February 7th 05 07:43 PM


All times are GMT +1. The time now is 09:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"