R
Ryan Janis
The challenge continues!!!
Well I've got some responses to my last entry and there
all great responses, but the one problem I had still
doesn't work. This is what I wrote in my last entry:
I have a workbook that has two spreadsheets in it.
Spreadsheet_1 and Spreadsheet_2. Spreadsheet_1 has
entries added to it daily. Spreadsheet_2 has a running
count of how many entries are in Spreadsheet_1. The
formula to get the total entries in Spreadsheet_1 to show
up on Spreadsheet_2 goes like this: =COUNT
('Spreadsheet_1'!A:A) That formula works well. Column A
in Spreadsheet_1 is a column of dates. How can I get a
total of just a certain date to show up on Spreadsheet_2?
Like let say I want to know the number of entries on
01/05/2004. What would that formula look like? I've
tried a number of things and cannot figure it out. The
other challenge is I would like to know the count of a
certain day, regardless of the year. So if I want to know
the number of entries on 01/05, in 1990-2004, how can I do
that?
The question that I have that still isn't working is the
count regardless of year. A couple of people have stepped
up to the challenge and giving me ideas to my problem, but
neither of them have turned out correct.
"Dan E" hooked me up with this formula:
=SUMPRODUCT((MONTH(Sheet1!A1:A65000)=5)*(DAY(Sheet1!
A1:A65000)=12))
and "Jim" hooked me up with this fancy formula as well:
To count all January 5ths regardless of year use a "helper
column" and
=AND(MONTH(A2)=1,DAY(A2)=5) and use COUNTIF
(HelperColumn=TRUE)
I've tried both of them, and they just can't seem to work
correctly. To give you more information of my dilemma, I
have a column in spreadsheet_1 that contains dates
(example: 12/20/03). There are more then one year like
12/20/02, 12/20/03, 12/20/04. There are a number of
different dates, but I want a count of all the 12/20's
regardless of the year, I don't care about the year, just
that specific date "12/20". This date column is in
column "A". Then text category is set to "Date" and the
format is "dd/mm/yy". The count needs to show up on
spreadsheet_2, not spreadsheet_1. Spreadsheet_2 is like
my stats sheet; it's like an overview of spreadsheet_1.
The first formula that I got from "Dan E" gives me a
#VALUE! Error. If I change the range to A:A in that
formula I get a #NUM! Error. The second formula by "Jim"
is just a little complicated to understand if I can get
that explained to me more that would be great, it look as
though he wants me to set up column "B" for
his "HelperColumn" but that is being occupied by data, is
it possible for me to set it up on another column? Anyway
if anyone has any suggestions, or know of a couple tricks
send them my way. As for "Jim" and "Dan E", thanks for
the help; you got me going down the right track.
P.S. If you would like to look at my last enties, there
on page 3 as I write this letter to you now. So just jump
3 pages back from this entry and you should see them.
Thanks
Well I've got some responses to my last entry and there
all great responses, but the one problem I had still
doesn't work. This is what I wrote in my last entry:
I have a workbook that has two spreadsheets in it.
Spreadsheet_1 and Spreadsheet_2. Spreadsheet_1 has
entries added to it daily. Spreadsheet_2 has a running
count of how many entries are in Spreadsheet_1. The
formula to get the total entries in Spreadsheet_1 to show
up on Spreadsheet_2 goes like this: =COUNT
('Spreadsheet_1'!A:A) That formula works well. Column A
in Spreadsheet_1 is a column of dates. How can I get a
total of just a certain date to show up on Spreadsheet_2?
Like let say I want to know the number of entries on
01/05/2004. What would that formula look like? I've
tried a number of things and cannot figure it out. The
other challenge is I would like to know the count of a
certain day, regardless of the year. So if I want to know
the number of entries on 01/05, in 1990-2004, how can I do
that?
The question that I have that still isn't working is the
count regardless of year. A couple of people have stepped
up to the challenge and giving me ideas to my problem, but
neither of them have turned out correct.
"Dan E" hooked me up with this formula:
=SUMPRODUCT((MONTH(Sheet1!A1:A65000)=5)*(DAY(Sheet1!
A1:A65000)=12))
and "Jim" hooked me up with this fancy formula as well:
To count all January 5ths regardless of year use a "helper
column" and
=AND(MONTH(A2)=1,DAY(A2)=5) and use COUNTIF
(HelperColumn=TRUE)
I've tried both of them, and they just can't seem to work
correctly. To give you more information of my dilemma, I
have a column in spreadsheet_1 that contains dates
(example: 12/20/03). There are more then one year like
12/20/02, 12/20/03, 12/20/04. There are a number of
different dates, but I want a count of all the 12/20's
regardless of the year, I don't care about the year, just
that specific date "12/20". This date column is in
column "A". Then text category is set to "Date" and the
format is "dd/mm/yy". The count needs to show up on
spreadsheet_2, not spreadsheet_1. Spreadsheet_2 is like
my stats sheet; it's like an overview of spreadsheet_1.
The first formula that I got from "Dan E" gives me a
#VALUE! Error. If I change the range to A:A in that
formula I get a #NUM! Error. The second formula by "Jim"
is just a little complicated to understand if I can get
that explained to me more that would be great, it look as
though he wants me to set up column "B" for
his "HelperColumn" but that is being occupied by data, is
it possible for me to set it up on another column? Anyway
if anyone has any suggestions, or know of a couple tricks
send them my way. As for "Jim" and "Dan E", thanks for
the help; you got me going down the right track.
P.S. If you would like to look at my last enties, there
on page 3 as I write this letter to you now. So just jump
3 pages back from this entry and you should see them.
Thanks