| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Formula - Count Ticks | Excel Discussion (Misc queries) | |||
| is there a formula to count something like this.... | Excel Worksheet Functions | |||
| Trying to construct a count count formula | Excel Discussion (Misc queries) | |||
| Count if formula | Excel Discussion (Misc queries) | |||
| Count Formula | Excel Worksheet Functions | |||
Hybrid Mode
