1. ## count months (2000)

Hi, if I had dates in A2:A200 wrote like this (3-Jan-06) on sheet 1 and on sheet 2 in a2:a12 I had the months wrote like this (January) what would the formula be on sheet 2 B2 letting me know who many people we see in the month of January, February and so on?
Thanks for any help

2. ## Re: count months (2000)

Enter the following as an array formula in B2 (confirm with Ctrl+Shift+Enter instead of just Enter):
<code>
=SUM(1*(TEXT(Sheet1!\$A\$2:\$A\$200,"mmmm")=A2))
</code>
and fill down to B13 (I assume December is in A13, not A12)

3. ## Re: count months (2000)

Hans,
Would Sumif work?
Thanks

4. ## Re: count months (2000)

You could use a countif:
=COUNTIF(Sheet1!\$A\$2:\$A\$200,">="&DATEVALUE(A2&"1, 2006"))-COUNTIF(Sheet1!\$A\$2:\$A\$200,">"&DATE(2006,MONTH(DAT EVALUE(A2&" 1, 2006"))+1,1)-1)

[It could be less complicated if instead of months in Sheet2!A2:A13 you used actual dates (could be formatted to display the month), or month numbers]
Steve

5. ## Re: count months (2000)

Since you are asking about SUMIF, is it safe to assume that there is another column with a patient count involved?

The following assumes that column B has a patient count for each date in column A

If your month labels are text:
=SUMPRODUCT((TEXT(Sheet1!\$A\$2:\$A\$200,"mmmm")=A2)*S heet1!\$B\$2:\$B\$200)

If you month labels are dates and formatted as sdckap mentions:
=SUMPRODUCT((MONTH(Sheet1!\$A\$2:\$A\$200)=MONTH(H2))* Sheet1!\$B\$2:\$B\$200)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•