challenge for everyone!

  • Thread starter Thread starter Ryan Janis
  • Start date Start date
R

Ryan Janis

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?
 
Ryan,

For your first problem:

A COUNTIF can be used to specify one condition:

=COUNTIF(Spreadsheet_1!A:A,"May 12, 2002")

"May 12, 2002" can be any valid date string or a reference to a
cell containing a valid date.

For your second problem:

A SUMPRODUCT can be used to count numerous conditions.

=SUMPRODUCT((MONTH(Sheet1!A1:A65000)=5)*(DAY(Sheet1!A1:A65000)=12))
counts all May 12ths

=SUMPRODUCT((MONTH(Sheet1!A1:A65535)=5)*(DAY(Sheet1!A1:A65535)=12)
*(YEAR(Sheet1!A1:A65535)>1992)*(YEAR(Sheet1!A1:A65535)<2005))
counts all May 12ths between 1992 and 2005

Unfortunately the Sheet1!A:A will not work as an entire column
may not be selected. You can however use the range:
A1:A65535 which will exclude only the last cell.

Dan E
 
=COUNTIF(Sheet1!A2:B1000,"=1/5/04") to count all entries for a certain date.
To count entries for a range of dates:
=COUNTIF(Sheet1!A2:B1000,">=1/1/04")-COUNTIF(Sheet1!A2:B1000,"=1/1/08")
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)
 
Back
Top