asked on 5/19/2011

Simple countif but im stuck

I have a list of date formatted data in column G3:G500 on sheet 2. I would like to count in cell B5 how many dates are in January and C5 how many dates are in February. Sheet 1)

In B4 I have the 31 Jan 2010 as a ref and in C4 28th Feb 2010 and I would like to write something like:

=COUNTIF('sheet1'!G$3:$G$20000,<=B4)

Anybody can help please as I was sitting a few hours on this already :(

Thanks

Rainer

Microsoft Excel

Last Comment

RainerWiegand

5/19/2011

ASKER CERTIFIED SOLUTION

Saqib Husain

5/19/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Rory Archibald

5/19/2011

Don't forget that for Feb you will need to subtract January's data from the total. I would personally use SUMPRODUCT:

=SUMPRODUCT(--(YEAR('sheet1'!G$3:$G$20000)=Year(B4)),--(MONTH('sheet1'!G$3:$G$20000)=MONTH(B4)))

assuming you want to differentitate between Jan 2010 and Jan 2011. If not, then just:
=SUMPRODUCT(--(MONTH('sheet1'!G$3:$G$20000)=MONTH(B4)))

RainerWiegand

5/19/2011

ASKER

Great help only I had to substract the previous month for the following cells.

Thank you very much

Rainer

barry houdini

5/19/2011

barry houdini

5/19/2011

Be careful with that last one - for January only blanks in G3:G20000 will be counted - to avoid that either use Rory's previous suggestion including the year criterion...or explicitly exclude blanks, i.e.

=SUMPRODUCT(--('sheet1'!G$3:$G$20000<>""),--(MONTH('sheet1'!G$3:$G$20000)=MONTH(B4)))

regards, barry

RainerWiegand

5/19/2011

ASKER

=COUNTIF('RAW DATA'!$G$3:$G$20000,"<="&R4)-SUM(B5:Q5)

and i did the same for all other cells. Basically I am substracting the previous month and it work fine as each date is a higher value.

Rory Archibald

5/19/2011

As long as all your data is in one year, or you do just want a summary for say January regardless if year, that's fine.

Rory Archibald

5/19/2011

I should clarify: that only applies to the first date, as you pick up everything less than that date.

RainerWiegand

5/19/2011

ASKER

The first date is picked up from the first data value in sheet 2 G3 and converted to the last day of the month:

=EOMONTH(D4,0)

and it does not conflict with the next year as 2010 starts as value 40179 and 2011 with 40544.

Thanks again :)

I hop this is right

Regards

Rainer

Rory Archibald

5/19/2011

Yes, that's fine then. :)

RainerWiegand

5/19/2011

ASKER

=DATE(YEAR(D4),MONTH(D4)+1,0)

that the right one :)