• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count column F Y values for each seperate month in column A

T

tpheath

New Member
Can't get this to work.


Column A5:A125 contains dates (format: January, 12 2010) Column F5:F125 contains "Y" or "N". I need to count all of the Y values that fall in each month of the year.


One big issue that I am having is that the column A is formatted as a date. When there are blank cells, Excel is seeing them as January, 0 1900. This throws off the simple counting of January occurances. I have dealt with this when just counting the number of instances in 1 month, but throws me off even further when trying to count the number instances in a month that also contain a Y value in F.


Any help would be appreciated!!!
 
Try

=SUMPRODUCT(--(A5:A125>=H5)*(A5:A125<=H6)*(F5:F125="Y"))


H5 has start of month date 1/3/2010

H6 has end of month date 31/3/2010


If you only want to enter 1 date use


=SUMPRODUCT(--(A5:A125>=(EOMONTH(H5,-1)+1))*(A5:A125<=EOMONTH(H5,0))*(F5:F125="Y"))

Where H5 can have any date in the month you are interested in eg 12/3/2010 for March 2010
 
Is it possible to do this without referencing cells with entered dates? This spreadsheet is duplicated for each, of a large number, of clients. It will also be used year after year. I really want to create it and not have to go back and update the dates so that it picks up the new year.
 
Like?


=SUMPRODUCT(--(A5:A125>=Date(2010,3,1))*(A5:A125<=Date(2010,3,31))*(F5:F125="Y"))
 
I have been able to figure out a formula for all of the months, except January. January is still counting the blank cells as January. Any ideas?


Here is my formula for Feb.

=SUMPRODUCT(--(TEXT(A6:A126,"mmm")="Feb")*(F5:F125="Y"))
 
I don't think you can put a range into the Text Function like "TEXT(A5:A20,"mmm")"


Try this

=SUMPRODUCT(--(MONTH(A5:A125)=1)*(F5:F125="Y"))
 
OK, one more bug. I am also counting all instances of January in column A. This goes back to the blank cells being formatted as dates (1/0/1900). As a result, the formula is counting all of the blank cells as January. Interestingly enough, I can count blank cells and then use that number and subtract from the below formula to get what I need. I would just like to do this in one formula, without having to hide anything, if possible.


I am using.

=SUMPRODUCT(--(MONTH(A5:A125)=1))
 
Are you sure you have blank cells?

Blank cells should be showing up as 1/0/1900


Do you have Zeroes in the cells and a cell format where they are showing as blank ?

or even spaces in them, something doesn't sound right
 
They are blank cells and showing up as 1/0/1900, so that when the formula is counting all of the 1 months, it is counting the blank cells. Is there a way to count all of January when the year is greater than 2000, or the day is greater than 0? Completely stuck on this January thing!


Thanks for your continued help!
 
You must log in or register to reply here.
Back
Top