Counting non-blank cells with conditions

[Saylindara]

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?

 

[Luke M]

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.

 

[p45cal]

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|
+-------------------------------------------------------------------+

 

[Saylindara]

This worked perfectly thank you

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]

Thisworked perfectly too, thank you