Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Paul
 
Posts: n/a
Default Need assistance: Count non-blank within date range

I am building a call log sheet for a friend. He needs to log each phone call
he makes each month, and track numbers of call-back requests.

There are 2 columns in question. Column E is the date that the phone call
was originally made. Every used row will have a date in col E. Column F
contains a note related to the call-back request. If there is no further
action required, the cell in col F will be blank.

My goal is to have a function (probably array function) to count the number
of non-blank cells in col F for each month, as noted in col E of the phone
call date. The analysis will appear on a separate sheet.

Example source data:
E F
2/1/2006 call back 5/4
2/1/2006
2/16/2006 send fax
2/27/2006
3/1/2006
3/7/2006 call back 4/1
3/8/2006 email pricelist
3/9/2006
3/14/2006
3/18/2006 call back 5/12

Example Results:

Feb. 2006: 2
Mar. 2006: 3

I am currently using array function
{=SUM((MONTH(Data!$E$3:$E$6000)=$A3)*(YEAR(Data!$E $3:$E$6000)=$A$2))} to
simply count the number of calls made, but am stumped as far as counting
non-blank cells adjacent to this column.

Thank you for your assistance.




  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
 
Posts: n/a
Default Need assistance: Count non-blank within date range

Time to meet SUMPRODUCT

In place of
{=SUM((MONTH(Data!$E$3:$E$6000)=$A3)*(YEAR(Data!$E $3:$E$6000)=$A$2))} Use
=SUMPRODUCT
(--(MONTH(Data!$E$3:$E$6000)=$A3), --(YEAR(Data!$E$3:$E$6000)=$A$2)) This
is NOT an array formula so just complete it with ENTER

For non-blanks
=SUMPRODUCT
(--(MONTH(Data!$E$3:$E$6000)=$A3), --(YEAR(Data!$E$3:$E$6000)=$A$2), --(Data!$F$3:$F$6000""))

For more info see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
explains the "--":
http://mcgimpsey.com/excel/formulae/doubleneg.html


best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Paul" wrote in message
...
I am building a call log sheet for a friend. He needs to log each phone
call he makes each month, and track numbers of call-back requests.

There are 2 columns in question. Column E is the date that the phone call
was originally made. Every used row will have a date in col E. Column F
contains a note related to the call-back request. If there is no further
action required, the cell in col F will be blank.

My goal is to have a function (probably array function) to count the
number of non-blank cells in col F for each month, as noted in col E of
the phone call date. The analysis will appear on a separate sheet.

Example source data:
E F
2/1/2006 call back 5/4
2/1/2006
2/16/2006 send fax
2/27/2006
3/1/2006
3/7/2006 call back 4/1
3/8/2006 email pricelist
3/9/2006
3/14/2006
3/18/2006 call back 5/12

Example Results:

Feb. 2006: 2
Mar. 2006: 3

I am currently using array function
{=SUM((MONTH(Data!$E$3:$E$6000)=$A3)*(YEAR(Data!$E $3:$E$6000)=$A$2))} to
simply count the number of calls made, but am stumped as far as counting
non-blank cells adjacent to this column.

Thank you for your assistance.






  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Kevin Vaughn
 
Posts: n/a
Default Need assistance: Count non-blank within date range

On my test range, this worked:

SUMPRODUCT(--(MONTH(Data!$E$1:$E$10)=MONTH(Test!$A3)),--(YEAR(Data!$E$1:$E$10)=YEAR(Test!$A3)),--(Data!$F$1:$F$10<""))

--
Kevin Vaughn


"Paul" wrote:

I am building a call log sheet for a friend. He needs to log each phone call
he makes each month, and track numbers of call-back requests.

There are 2 columns in question. Column E is the date that the phone call
was originally made. Every used row will have a date in col E. Column F
contains a note related to the call-back request. If there is no further
action required, the cell in col F will be blank.

My goal is to have a function (probably array function) to count the number
of non-blank cells in col F for each month, as noted in col E of the phone
call date. The analysis will appear on a separate sheet.

Example source data:
E F
2/1/2006 call back 5/4
2/1/2006
2/16/2006 send fax
2/27/2006
3/1/2006
3/7/2006 call back 4/1
3/8/2006 email pricelist
3/9/2006
3/14/2006
3/18/2006 call back 5/12

Example Results:

Feb. 2006: 2
Mar. 2006: 3

I am currently using array function
{=SUM((MONTH(Data!$E$3:$E$6000)=$A3)*(YEAR(Data!$E $3:$E$6000)=$A$2))} to
simply count the number of calls made, but am stumped as far as counting
non-blank cells adjacent to this column.

Thank you for your assistance.





  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SteveG
 
Posts: n/a
Default Need assistance: Count non-blank within date range


SUMPRODUCT should work.

=SUMPRODUCT(--(MONTH(E1:E9)=1),--(YEAR(E1:E9)=2006),--(F1:F9<""))

This is for January 2006. You need to change the MONTH()=1 number for
each month accordingly, Feb = 2, March = 3 and so on. You could put
the numbers in a reference cell and have it refer to that instead.

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=515024

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Paul
 
Posts: n/a
Default Need assistance: Count non-blank within date range

Bernard, Kevin and Steve - Thank you for your replies. SUMPRODUCT seems to
do exactly what I need. I'll have to research that function further to see
what other applications it may have in my work.

Thanks again.
Paul




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Kevin Vaughn
 
Posts: n/a
Default Need assistance: Count non-blank within date range

Here is a link to an explanation of sumproduct. I had no idea how to use it
before I read this page:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Kevin Vaughn


"Paul" wrote:

Bernard, Kevin and Steve - Thank you for your replies. SUMPRODUCT seems to
do exactly what I need. I'll have to research that function further to see
what other applications it may have in my work.

Thanks again.
Paul



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
How do I count items within a date range in Excel? tcolbert Excel Worksheet Functions 2 January 9th 06 07:01 PM
how to check if date falls within range Bharat Saboo Excel Worksheet Functions 4 December 30th 05 11:31 AM
Date range criteria and Pivot tables (again!) DerbyJim1978 Excel Worksheet Functions 3 July 13th 05 10:14 PM
Count the occurances of a month in a range of date fields Keith Brown Excel Worksheet Functions 8 March 14th 05 12:24 PM
count weekdays in a date range benb Excel Worksheet Functions 1 January 13th 05 03:49 PM


All times are GMT +1. The time now is 10:01 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"