| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count number of cells of a particular month in a column of dates
Cells D2-D15 contains a list of dates ranging from Jan through May. I need D16 to count how many cells in that column contain dates in March. I am assuming it is a COUNTIF function but I have not been able to find a solution in any texts or on the internet. Any type of help is appreciated. Gohan51D -- Gohan51D ------------------------------------------------------------------------ Gohan51D's Profile: http://www.excelforum.com/member.php...o&userid=32274 View this thread: http://www.excelforum.com/showthread...hreadid=521940 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count number of cells of a particular month in a column of dates
Gohan51D wrote:
Cells D2-D15 contains a list of dates ranging from Jan through May. I need D16 to count how many cells in that column contain dates in March. I am assuming it is a COUNTIF function but I have not been able to find a solution in any texts or on the internet. Any type of help is appreciated. Gohan51D =SUMPRODUCT((MONTH(D2:D15)=3)*1) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count number of cells of a particular month in a column of dates
If you don't care about the year you could use =SUMPRODUCT(--(MONTH(D2:D15)=3)) where 3 = March be careful if you try to count for January because blank cells will be included in the count so you need to alter to =SUMPRODUCT(--ISNUMBER(D2:D15),--(MONTH(D2:D15)=1)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521940 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count number of cells of a particular month in a column of dates
Thanks I will give them a try Gohan51D -- Gohan51D ------------------------------------------------------------------------ Gohan51D's Profile: http://www.excelforum.com/member.php...o&userid=32274 View this thread: http://www.excelforum.com/showthread...hreadid=521940 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count number of cells of a particular month in a column of dates
The formula seems to work but I didn't mention that the column of dates also contains text. When I remove the text the formula works great but once the text is entered I get #VALUE!, how can I ignore all text. Gohan51D -- Gohan51D ------------------------------------------------------------------------ Gohan51D's Profile: http://www.excelforum.com/member.php...o&userid=32274 View this thread: http://www.excelforum.com/showthread...hreadid=521940 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count number of cells of a particular month in a column of dates
Maybe you could use something like:
=SUM(IF(ISNUMBER(D2:D15),--(MONTH(D2:D15)=1))) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column Gohan51D wrote: The formula seems to work but I didn't mention that the column of dates also contains text. When I remove the text the formula works great but once the text is entered I get #VALUE!, how can I ignore all text. Gohan51D -- Gohan51D ------------------------------------------------------------------------ Gohan51D's Profile: http://www.excelforum.com/member.php...o&userid=32274 View this thread: http://www.excelforum.com/showthread...hreadid=521940 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count number of cells of a particular month in a column of dates
The formula seems to work but I didn't mention that the column of dates also contains text. When I remove the text the formula works great but once the text is entered I get #VALUE!, how can I ignore all text. This is the formula used. If any text in D2-D19 then I get #VALUE! If not text it works great. =SUMPRODUCT(--ISNUMBER(D2:D19),--(MONTH(D2:D19)=2)*1) =SUM(IF(MONTH(E2:E18)=2,1,0)) This also worked but with the same text problem. Gohan51D -- Gohan51D ------------------------------------------------------------------------ Gohan51D's Profile: http://www.excelforum.com/member.php...o&userid=32274 View this thread: http://www.excelforum.com/showthread...hreadid=521940 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count number of cells of a particular month in a column of dates
What happens when you tried the formula I suggested?
Gohan51D wrote: The formula seems to work but I didn't mention that the column of dates also contains text. When I remove the text the formula works great but once the text is entered I get #VALUE!, how can I ignore all text. This is the formula used. If any text in D2-D19 then I get #VALUE! If not text it works great. =SUMPRODUCT(--ISNUMBER(D2:D19),--(MONTH(D2:D19)=2)*1) =SUM(IF(MONTH(E2:E18)=2,1,0)) This also worked but with the same text problem. Gohan51D -- Gohan51D ------------------------------------------------------------------------ Gohan51D's Profile: http://www.excelforum.com/member.php...o&userid=32274 View this thread: http://www.excelforum.com/showthread...hreadid=521940 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count number of cells of a particular month in a column of dates
This is the one that finally worked for me =SUM(IF(--ISNUMBER(D2:D18),--(MONTH(D2:D18)=3)*1)) You were right Dave Thanks for the help it is greatly appreciated. Gohan51D -- Gohan51D ------------------------------------------------------------------------ Gohan51D's Profile: http://www.excelforum.com/member.php...o&userid=32274 View this thread: http://www.excelforum.com/showthread...hreadid=521940 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count number of cells of a particular month in a column of dates
I don't think you need that *1. The -- stuff changes the trues and falses to
1's and 0's. Gohan51D wrote: This is the one that finally worked for me =SUM(IF(--ISNUMBER(D2:D18),--(MONTH(D2:D18)=3)*1)) You were right Dave Thanks for the help it is greatly appreciated. Gohan51D -- Gohan51D ------------------------------------------------------------------------ Gohan51D's Profile: http://www.excelforum.com/member.php...o&userid=32274 View this thread: http://www.excelforum.com/showthread...hreadid=521940 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count if date is between two dates and value in another column equ | Excel Worksheet Functions | |||
| How to count the number of times something occurs within a certain month | Excel Worksheet Functions | |||
| Count cells based on date range in another column | New Users to Excel | |||
| How do I count cells in a column of dates between date ranges? | Excel Worksheet Functions | |||
| GET.CELL | Excel Worksheet Functions | |||
Linear Mode
