count formula
 

I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that match the month and year of a cell. How
can I write the formula to calculate this count? Below is an example of what
I mean, A1 is the cell that I want the count from Col B to match where the
month and year is equal to 11/08. The answer should be 3. Can anyone help me
create this formula?
A1 = 11/25/08

Col B
4/27/07
9/4/07
1/2/08
2/11/08
4/25/08
5/13/08
5/14/08
5/20/08
5/27/08
5/29/08
5/29/08
6/2/08
6/3/08
6/10/08
6/23/08
7/7/08
8/6/08
8/26/08
8/28/08
9/8/08
10/3/08
10/3/08
10/7/08
10/10/08
10/14/08
10/24/08
10/24/08
11/5/08
11/7/08
11/7/08

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

count formula
 

Use SUMPRODUCT:

=SUMPRODUCT((MONTH(B1:B30)=MONTH($A$1))*(YEAR(B1:B 30)=YEAR($A$1)))


--
Gary''s Student - gsnu2007k


"AHizon via OfficeKB.com" wrote:

I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that match the month and year of a cell. How
can I write the formula to calculate this count? Below is an example of what
I mean, A1 is the cell that I want the count from Col B to match where the
month and year is equal to 11/08. The answer should be 3. Can anyone help me
create this formula?
A1 = 11/25/08

Col B
4/27/07
9/4/07
1/2/08
2/11/08
4/25/08
5/13/08
5/14/08
5/20/08
5/27/08
5/29/08
5/29/08
6/2/08
6/3/08
6/10/08
6/23/08
7/7/08
8/6/08
8/26/08
8/28/08
9/8/08
10/3/08
10/3/08
10/7/08
10/10/08
10/14/08
10/24/08
10/24/08
11/5/08
11/7/08
11/7/08

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

count formula
 

On Dec 4, 4:37 pm, "AHizon via OfficeKB.com" <u38169@uwe wrote:
I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that match the month and year of a
cell.

Try:

=sumproduct((month(B1:B30)=month(A1))*(year(B1:B30 )=year(A1)))


On Dec 4, 4:37*pm, "AHizon via OfficeKB.com" <u38169@uwe wrote:
I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that match the month and year of a
cell. *How
can I write the formula to calculate this count? *Below is an example of what
I mean, A1 is the cell that I want the count from Col B to match where the
month and year is equal to 11/08. The answer should be 3. *Can anyone
help me create this formula?
A1 = 11/25/08

Col B
4/27/07
9/4/07
1/2/08
2/11/08
4/25/08
5/13/08
5/14/08
5/20/08
5/27/08
5/29/08
5/29/08
6/2/08
6/3/08
6/10/08
6/23/08
7/7/08
8/6/08
8/26/08
8/28/08
9/8/08
10/3/08
10/3/08
10/7/08
10/10/08
10/14/08
10/24/08
10/24/08
11/5/08
11/7/08
11/7/08

count formula
 

=SUMPRODUCT(--(B1:B30-DAY(B1:B30)=A1-DAY(A1)))



"AHizon via OfficeKB.com" wrote:

I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that match the month and year of a cell. How
can I write the formula to calculate this count? Below is an example of what
I mean, A1 is the cell that I want the count from Col B to match where the
month and year is equal to 11/08. The answer should be 3. Can anyone help me
create this formula?
A1 = 11/25/08

Col B
4/27/07
9/4/07
1/2/08
2/11/08
4/25/08
5/13/08
5/14/08
5/20/08
5/27/08
5/29/08
5/29/08
6/2/08
6/3/08
6/10/08
6/23/08
7/7/08
8/6/08
8/26/08
8/28/08
9/8/08
10/3/08
10/3/08
10/7/08
10/10/08
10/14/08
10/24/08
10/24/08
11/5/08
11/7/08
11/7/08

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

count formula
 

Hi,

You could use the following array entered formula

=SUM(N(EOMONTH(A1,-1)=B1:B30-DAY(B1:B30)))
or
=SUM(N(B1:B30-DAY(B1:B30)=A1-DAY(A1)))

To enter these press Shift+Ctrl+Enter.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"AHizon via OfficeKB.com" wrote:

I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that match the month and year of a cell. How
can I write the formula to calculate this count? Below is an example of what
I mean, A1 is the cell that I want the count from Col B to match where the
month and year is equal to 11/08. The answer should be 3. Can anyone help me
create this formula?
A1 = 11/25/08

Col B
4/27/07
9/4/07
1/2/08
2/11/08
4/25/08
5/13/08
5/14/08
5/20/08
5/27/08
5/29/08
5/29/08
6/2/08
6/3/08
6/10/08
6/23/08
7/7/08
8/6/08
8/26/08
8/28/08
9/8/08
10/3/08
10/3/08
10/7/08
10/10/08
10/14/08
10/24/08
10/24/08
11/5/08
11/7/08
11/7/08

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

count formula
 

Great, this formula worked. Thanks so much!

Gary''s Student wrote:
Use SUMPRODUCT:

=SUMPRODUCT((MONTH(B1:B30)=MONTH($A$1))*(YEAR(B1: B30)=YEAR($A$1)))

I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that match the month and year of a cell. How
[quoted text clipped - 35 lines]
11/7/08
11/7/08

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1