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?

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?

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

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?

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