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

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

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

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

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

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

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

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

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
--

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

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

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

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

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
--

Count certain months
 

Glad to hear you got it sorted out !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--