Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count items within a date range in Excel? | Excel Worksheet Functions | |||
how to check if date falls within range | Excel Worksheet Functions | |||
Date range criteria and Pivot tables (again!) | Excel Worksheet Functions | |||
Count the occurances of a month in a range of date fields | Excel Worksheet Functions | |||
count weekdays in a date range | Excel Worksheet Functions |