I want to count months in a range?

  • Thread starter Thread starter 41roblynway
  • Start date Start date
4

41roblynway

Hi, I have a worksheet that has a column of when I finished auditing a
file. I want to track on another worksheet how many files I finished
in a particular month. So, with the column below, I would want a
formula that would count how many files I finished in march 2009.
Ideas? I've experimented with countif and sumif and month(), but
nothing has really worked. Help! Thanks!

Finished auditing File
1 jan 2009
3 Mar 2009
16 mar 2009
1 april 2009
 
Put 1 to 12 in cells A1:A12 on your second worksheet (to represent
months), and then in B1 of that sheet you can use this formula:

=SUMPRODUCT(--(MONTH('Audit sheet name'!F$1:F$100)=A1)

where I have assumed column F is where your finished date is, and that
you have 100 rows of data - adjust to suit, then copy down to B12.

Hope this helps.

Pete
 
This will do it:
=SUMPRODUCT(--(MONTH(A1:A14)=3))
provided you have real dates (not text) in that column

Place this somewhere on the worksheet with the data
Now Cut and Paste to the second worksheet; the references will update for
you.

Or make the formula in the second worksheet but rather than type A1:a14 (or
whatever) use the point method - click on the worksheet with the data and
use the mouse to select the range

best wishes
 
=sumproduct(--(text(a1:a100,"yyyymm"))="200903")
will count the number of dates in March of 2009.

If you wanted just the number of dates in March of any year, I'd use:
=sumproduct(--(month(a1:a100)=3,--(isnumber(a1:a100))

Without the =isnumber() portion, you'll find that empty cells are counted as
January.

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

========
You may want to add another column with formulas like:
=text(a2,"yyyy-mm")
and then sort your data and use Data|Subtotals

Or even spend a little time learning about pivottables. You can get some very
impressive summary reports and even group dates by month (or month and year).
 
Back
Top