Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
Is there a way to count the number of times an entree is entered in a
spreadsheet. example In row c i have dates entered 11/1 11/5 12/6 12/9 11/3 etc. Is there a way to count the number of times that the month of dec was entered. thanks in advance scott |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
Try the following array formula:
=SUM(IF(MONTH(A1:A5)=12,1,0)) commit it by hitting Ctrl-Shift-Enter - John www.JohnMichl.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
Scott, A1:A10 as range. =SUMPRODUCT(--(MONTH(A1:A10)=12)) Cheers, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=498019 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
John did not work not
"John Michl" wrote: Try the following array formula: =SUM(IF(MONTH(A1:A5)=12,1,0)) commit it by hitting Ctrl-Shift-Enter - John www.JohnMichl.com |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
Steve, sorry I couldnt seem to get that to work either. Is there a way to
change a date say 12/1 to one by using an if formula thanks scott maybe i am doing something wrong "SteveG" wrote: Scott, A1:A10 as range. =SUMPRODUCT(--(MONTH(A1:A10)=12)) Cheers, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=498019 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
Scott, I tried again on my computer and both solutions worked fine. I believe that for both solutions presented, you need to be sure you have certain AddIns installed. You can do this by going to ToolsAdd-Ins. If you do not have the add-ins installed, the checkbox to the left will not be selected. Select all the Add-Ins available. They should be Analysis ToolPak, Analysis ToolPak-VBA, Conditional Sum Wizard etc... Select the checkbox to the left of each add-in and click OK. The install will take a minute for all. Once you have done this, try the formulas again. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=498019 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
Scott, make sure that the "dates" are actually entered as numbers not
text. An easy way to verify would be to reformat the cell to display it as a different type of date (i.e., mm/dd/yy, dd-mmm, etc). If the formating doesn't change, it would indicate the values are text strings not dates. If the values are not actually dates, you'll need to convert them or modify the formula. - John |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
Scott/John, Because I wasn't aware of it, I tried John's theory on the cells being formatted as text prior to the data being entered but both formulas still worked fine. Just an FYI. Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=498019 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
Perhaps try also:
=SUMPRODUCT(--(LEFT(A1:A5,SEARCH("/",A1:A5)-1)+0=12)) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
Steve
Thanks for the help I am not able to install the add ons at this time. This is a work project and I need the cd to install the add on's, I do not have access to the cd since my boss is out of town for the day, when he gets back I will give it a shot. thanks again Scott "SteveG" wrote: Scott, I tried again on my computer and both solutions worked fine. I believe that for both solutions presented, you need to be sure you have certain AddIns installed. You can do this by going to ToolsAdd-Ins. If you do not have the add-ins installed, the checkbox to the left will not be selected. Select all the Add-Ins available. They should be Analysis ToolPak, Analysis ToolPak-VBA, Conditional Sum Wizard etc... Select the checkbox to the left of each add-in and click OK. The install will take a minute for all. Once you have done this, try the formulas again. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=498019 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
John
How would I convert them. Formatting looks ok. scott "John Michl" wrote: Scott, make sure that the "dates" are actually entered as numbers not text. An easy way to verify would be to reformat the cell to display it as a different type of date (i.e., mm/dd/yy, dd-mmm, etc). If the formating doesn't change, it would indicate the values are text strings not dates. If the values are not actually dates, you'll need to convert them or modify the formula. - John |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
John
I figured out a way to do it, it may not be correct but it works. In an empty cell I used the month function this changes the dates entered into a number which ever month it is. So I have two different months on one sheet I just used countif it =what ever month it is and it works. Thanks for everything. It is amazing the things that excel can do, and even more amazing that guys and gals can figure this stuff out. IMy spreadsheets are pretty basic and some of these formulas that people have given me on here and great. thanks again and you may want to watch for my name because I am sure more questions will come, thanks again "John Michl" wrote: Scott, make sure that the "dates" are actually entered as numbers not text. An easy way to verify would be to reformat the cell to display it as a different type of date (i.e., mm/dd/yy, dd-mmm, etc). If the formating doesn't change, it would indicate the values are text strings not dates. If the values are not actually dates, you'll need to convert them or modify the formula. - John |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
Steve
I figured out a way to do it, it may not be correct but it works. In an empty cell I used the month function this changes the dates entered into a number which ever month it is. So I have two different months on one sheet I just used countif it =what ever month it is and it works. Thanks for everything. It is amazing the things that excel can do, and even more amazing that guys and gals can figure this stuff out. IMy spreadsheets are pretty basic and some of these formulas that people have given me on here and great. thanks again and you may want to watch for my name because I am sure more questions will come, thanks again "SteveG" wrote: Scott, I tried again on my computer and both solutions worked fine. I believe that for both solutions presented, you need to be sure you have certain AddIns installed. You can do this by going to ToolsAdd-Ins. If you do not have the add-ins installed, the checkbox to the left will not be selected. Select all the Add-Ins available. They should be Analysis ToolPak, Analysis ToolPak-VBA, Conditional Sum Wizard etc... Select the checkbox to the left of each add-in and click OK. The install will take a minute for all. Once you have done this, try the formulas again. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=498019 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
Max
I figured out a way to do it, it may not be correct but it works. In an empty cell I used the month function this changes the dates entered into a number which ever month it is. So I have two different months on one sheet I just used countif it =what ever month it is and it works. Thanks for everything. It is amazing the things that excel can do, and even more amazing that guys and gals can figure this stuff out. IMy spreadsheets are pretty basic and some of these formulas that people have given me on here and great. thanks again and you may want to watch for my name because I am sure more questions will come, thanks again "Max" wrote: Perhaps try also: =SUMPRODUCT(--(LEFT(A1:A5,SEARCH("/",A1:A5)-1)+0=12)) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count certain months
Glad to hear you got it sorted out !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why "datedif" function results sometimes negative numbers? | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
count number of months year to date | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions |