Using CountIF? 3

[BlueHorizon]

Hi everyone,

I have the following in Excel:

StartDate
1/21/2011
1/27/2011
2/1/2011
2/5/2011
2/15/2011
2/21/2011
3/2/2011
3/5/2011
3/11/2011

etc.
What formula would count the number of dates by month? I tried CountIF using "1/*" as the criteria but I can't get it work.

Any help is greatly greatly appreciated,
Thanks in advance!



Best,
Blue Horizon

 

[mintjulep]

Read faq68-5827 to understand why what you've done doesn't work.

If you want to use months the most straightforward way will be to extract the month value out of your dates into a helper column.

the =MONTH(date)function exists to do that.

 

[SkipVought]

hi,

FIRST, you need a list of dates by month.

The SIMPLEST way is to enter
[tt]
jan 2011
[/tt]
and Excel CONVERTS that text to a REAL DATE.

The interesting thing about THIS method is that you can DRAG the COPY HANDLE up, down, left or right, and the resulting dates will be THE FIRST OF THE MONTH incrimentally increasing or decreasing, depending on the direction of the drag.

So now, using that method, I have...
[tt]
Jan-11
Feb-11
Mar-11
Apr-11
[/tt]
that are EACH the first of the month REAL DATES and they start in C2.

Then there's for formula in the adjacent cell, D2, using a NAMED RANGE...
[tt]
=SUMPRODUCT((StartDate>=C2)*(StartDate<C3))
[/tt]
and the final result...
[tt]
Jan-11 2
Feb-11 4
Mar-11 3
Apr-11
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

can make use of the fact that sumproduct allows expressions to be evaluated against each row whilst countif does not:

=SUMPRODUCT((MONTH($A$2:$A$1000)=1)*(1))

will give you january count

=SUMPRODUCT((MONTH($A$2:$A$1000)=2)*(1))

will give you feb count

Also - you could simply put a pivottable over the top, group your dates by month and add a count of date into the VALUE field...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

and that only works IF the dates are all within ONE calendar year. Otherwise, jan, for instance, from multiple years will count.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

How are things down under, Geoff?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

spot on Skip - it is completely year agnostic!

Down under is rather wetter than I had been led to believe!! It's good though it does cause a little issue when attempting to answer threads here as by the time I get to 'em they're pretty much done and dusted.

Currently getting waaaay to gamiliar with SQL Server Integration Services so using TT as a refuge for my excel skills!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Well keep your powder dry, and your Excel skills sharp, my friend!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BlueHorizon]

You guys are the best!! Works like a charm - have a good day wherever you are!!

Thanks,
Kathy

Best,
Blue Horizon

 

[BlueHorizon]

Hi again!

This formula: =SUMPRODUCT((MONTH($A$2:$A$1000)=1)*(1))
seems to return a count of Jan (1), Oct (10), Nov (11) and Dec (12).

Is there a way to limit this formula to Jan (1)?

TIA,


Best,
Blue Horizon

 

[xlbo]

I'm not sure what you mean - the formula as set up should count only the dates in jan (Month = 1)

You would need to change the =1 to -2 to count fec =10 for oct etc etc...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Well, what do you know? The month function returns a 1 for blank cells. So the formula you posted works for all months except January where it adds a count of blank cells.

Sticking with sumproduct and a single formula for all months:
=SUMPRODUCT((MONTH($A$2:$A$1000)=E2)*(NOT(ISBLANK($A$2:$A$1000))))

Or simply adjust the january formula to deduct Countblank($A$2:$A$1000)


BlueHorizon: does that fix your issue?

Gavin

 

[BlueHorizon]

Hi xlbo and thanks in advance for your help. Here's what some of my results look like when I enter the formula you gave me, changing only the month variable after the = sign. Every count is correct except for January. Weird huh?

=SUMPRODUCT((MONTH($A$2:$A$1000)=1)*(1))


StartDate Count Month
1/2/2011 8 Jan
1/7/2011 5 Feb
1/12/2011 7 Mar
1/17/2011 5 Apr
1/22/2011 7 May
1/27/2011 3 Jun
2/1/2011 6 Jul
2/6/2011 3 Aug
2/11/2011 6 Sep
2/16/2011 5 Oct
2/21/2011 6 Nov
3/1/2011 5 Dec
3/3/2011
3/8/2011
3/13/2011
3/18/2011
3/23/2011
3/28/2011
4/2/2011
4/7/2011
4/17/2011
4/22/2011
4/23/2011
5/1/2011
5/2/2011
5/7/2011
5/12/2011
5/17/2011

I can work with this if I need to but it does seem strange....

Thanks,
Kathy



Best,
Blue Horizon

 

[combo]

You can try:
=SUMPRODUCT(-(MONTH($A$2:$A$1000)=1),-(DAY($A$2:$A$1000)>0))

But if there are no blank fields in data range, I'd use pivot table and group dates by month and year:
- StartDate as row field and data field with count aggregation formula,
- group by year and month,
- drag year aggregation to page area (and month aggregation too, depending on required layout).



combo

 

[SkipVought]

1) your RANGE must include ONLY the cells containg dates.

2) I almost ALWAYS use a DYNAMIC Named Range

3) enter the Month NUMBER to reference in your formula

4) the formula...
[tt]
=SUMPRODUCT((MONTH(StartDate)=B2)*(1))
[/tt]
5) the result using your posted data
[tt]
Month Count
1 6
2 5
3 7
4 5
5 5
6 0
7 0
8 0
9 0
10 0
11 0
12 0
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}