# 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
Saqib Husain

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.
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

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

Thank you very much

Rainer
barry houdini

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.
barry houdini

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

=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.
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.
I should clarify: that only applies to the first date, as you pick up everything less than that date.
RainerWiegand

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
Yes, that's fine then. :)
RainerWiegand

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

that the right one :)
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY