Hi guys, I'm really stuck at work, I can't seem to be to work this formula out.
Basically I've get a column which have some date surrounded by (some cell are blank though which is fine). The format is "00/00/0000", but I’m displaying the month as the format instead, so 05/03/2007 would be "March". I've get another worksheet near the months going down the not here mitt side. What I want is a total count of the date that crash inside the corresponding month. So if at hand are 15 cell that hold date inwardly March, I want it to count them subsequent to my 'March' cell. Can you back?
Many appreciation!
Answers: You will want to use an array formula:
{=SUM (IF (MONTH (column of dates)=MONTH (month),1,0))}
The inventory of cell near your month label will own to be enter as date for this to work:
1/1; 1/2; 1/3 etc.
use Format Cells Custom: mmm
for Jan; Feb; Mar - or
Format Cells Custom: mmmm
for January; February etc.
Let your date be surrounded by cell B7 to B24
Let your month name (but see above) be contained by G1 through G12
Then the array formula looks similar to this contained by cell H1:
{=SUM (IF (MONTH ($B$7:$B$24)=MONTH (G1),1,0))}
Type the formula short the curly brackets after hold down Ctrl and Shift and press Enter. The curly brackets will appear.
Drag the H1 formula down.
Job done.
Google/yahoo "array formula Excel" for some worrisome stuff...
Add to request for information if you encounter any probs - pious luck!
Apply a filter, the select Custom use option from 01/03/07 to 31/03/07 and next use sum to total them, a short time ago ask if you call for further explanation.
Another thought, insert a colunm subsequent to your month Title it Month Number, next for Jan use 01, Feb use 02, March 03, right up to Dec 12
Then you can use background, subtotal:
At respectively loose change in
Month Number
Use function
Count
Add subtotal to
Month Number
To do it try this:
You enjoy a chronicle of date within col A, and you want to count how abundant are contained by May. In Cell B1, enter the formula:
=MONTH(A1) and copy it down Col B as far as your catalogue go. Then count the # of date within May near the formula: =COUNTIF(B:B,5)