Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting non-blank cells with conditions
I have the following:
Referral received Appointment sent 3/8/09 31/8/09 4/8/09 2/9/09 5/8/09 2/9/09 3/9/09 4/9/09 5/9/09 I would like to count the number of appointments that were sent for referrals received in each month, e.g. for August it would be 2 and for September 1. Is that possible? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting non-blank cells with conditions
something like this:
=SUMPRODUCT(--(MONTH(A2:A10)=8),--(ISNUMBER(B2:B10))) Note that you can't callout entire columns (A:A) in SUMPRODUCT unless using XL 2007. Feel free to change the "8" to a cell reference, if desired. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Saylindara" wrote: I have the following: Referral received Appointment sent 3/8/09 31/8/09 4/8/09 2/9/09 5/8/09 2/9/09 3/9/09 4/9/09 5/9/09 I would like to count the number of appointments that were sent for referrals received in each month, e.g. for August it would be 2 and for September 1. Is that possible? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting non-blank cells with conditions
something along the lines of =SUMPRODUCT((MONTH($A$2:$A$7)=8)*($B$2:$B$7<"")) where the 8 represents August. See attached for a working example with small modification. Note that if there is more than 1 year in the first column then the count for each month will include that month from all the years in one value. +-------------------------------------------------------------------+ |Filename: Conditional count.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=222| +-------------------------------------------------------------------+ -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126146 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting non-blank cells with conditions
This worked perfectly thank you
"Luke M" wrote: something like this: =SUMPRODUCT(--(MONTH(A2:A10)=8),--(ISNUMBER(B2:B10))) Note that you can't callout entire columns (A:A) in SUMPRODUCT unless using XL 2007. Feel free to change the "8" to a cell reference, if desired. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Saylindara" wrote: I have the following: Referral received Appointment sent 3/8/09 31/8/09 4/8/09 2/9/09 5/8/09 2/9/09 3/9/09 4/9/09 5/9/09 I would like to count the number of appointments that were sent for referrals received in each month, e.g. for August it would be 2 and for September 1. Is that possible? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting non-blank cells with conditions
Thisworked perfectly too, thank you
"p45cal" wrote: something along the lines of =SUMPRODUCT((MONTH($A$2:$A$7)=8)*($B$2:$B$7<"")) where the 8 represents August. See attached for a working example with small modification. Note that if there is more than 1 year in the first column then the count for each month will include that month from all the years in one value. +-------------------------------------------------------------------+ |Filename: Conditional count.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=222| +-------------------------------------------------------------------+ -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126146 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Non Blank Cells | Excel Discussion (Misc queries) | |||
Counting Non Blank Cells | New Users to Excel | |||
Counting non-blank cells | Excel Discussion (Misc queries) | |||
Counting blank cells | Excel Discussion (Misc queries) | |||
counting cells in a data range that meet 3 specific conditions | Excel Discussion (Misc queries) |