Hola fellow Excel Kings & Queens~

Please let me know if there is a way to do the following:
Count the number of entries per quarter of data listed in a column.

I've thought about setting a < of entries falling between set date
criteria, but unsure how to do this. Column is currently formatted in
mm-dd-yy. I don't understand enough about converting date info so that I
might get the infor I'm after, but I have a hunch that I might have to go
down that road. :-)

Equation can be seperated into 4 seperate equations to keep things
simplified, the data is just ongoing and the results get put into
graphs/charts.

Your help is appreciated.

Try this:

Use cells to hold your date boundaries:

C1 = start date
D1 = end date

=COUNTIF(A1:A100,"="&C1)-COUNTIF(A1:A100,""&D1)

--
Biff
Microsoft Excel MVP


"Dvinechild" wrote in message
...
Hola fellow Excel Kings & Queens~

Please let me know if there is a way to do the following:
Count the number of entries per quarter of data listed in a column.

I've thought about setting a < of entries falling between set date
criteria, but unsure how to do this. Column is currently formatted in
mm-dd-yy. I don't understand enough about converting date info so that I
might get the infor I'm after, but I have a hunch that I might have to go
down that road. :-)

Equation can be seperated into 4 seperate equations to keep things
simplified, the data is just ongoing and the results get put into
graphs/charts.

Your help is appreciated.

Hi,

If i've understood correctly and with your dates in column A put this in a
cell and arrray enter and drag down 3 rows to sum Q1 to g4 dates

=COUNT(IF(INT((MONTH(A1:A100)/4)+1)=ROW(A1),IF(A1:A100<"",A1:A100)))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Dvinechild" wrote:

Hola fellow Excel Kings & Queens~

Please let me know if there is a way to do the following:
Count the number of entries per quarter of data listed in a column.

I've thought about setting a < of entries falling between set date
criteria, but unsure how to do this. Column is currently formatted in
mm-dd-yy. I don't understand enough about converting date info so that I
might get the infor I'm after, but I have a hunch that I might have to go
down that road. :-)

Equation can be seperated into 4 seperate equations to keep things
simplified, the data is just ongoing and the results get put into
graphs/charts.

Your help is appreciated.

oops,

needs to be absolute

=COUNT(IF(INT((MONTH($A$1:$A$100)/4)+1)=ROW(A1),IF($A$1:$A$100<"",$A$1:$A$100)))

Mike

"Dvinechild" wrote:

Hola fellow Excel Kings & Queens~

Please let me know if there is a way to do the following:
Count the number of entries per quarter of data listed in a column.

I've thought about setting a < of entries falling between set date
criteria, but unsure how to do this. Column is currently formatted in
mm-dd-yy. I don't understand enough about converting date info so that I
might get the infor I'm after, but I have a hunch that I might have to go
down that road. :-)

Equation can be seperated into 4 seperate equations to keep things
simplified, the data is just ongoing and the results get put into
graphs/charts.

Your help is appreciated.

Thanks Mike, I'm working on this but want to know if I can alter the equation
to read the entire column instead of seemingly stopping at row 100? I also
need to refer to another tab, do I enter that reference before the start of
the absolutes?

"Mike H" wrote:

oops,

needs to be absolute

=COUNT(IF(INT((MONTH($A$1:$A$100)/4)+1)=ROW(A1),IF($A$1:$A$100<"",$A$1:$A$100)))

Mike

"Dvinechild" wrote:

Hola fellow Excel Kings & Queens~

Please let me know if there is a way to do the following:
Count the number of entries per quarter of data listed in a column.

I've thought about setting a < of entries falling between set date
criteria, but unsure how to do this. Column is currently formatted in
mm-dd-yy. I don't understand enough about converting date info so that I
might get the infor I'm after, but I have a hunch that I might have to go
down that road. :-)

Equation can be seperated into 4 seperate equations to keep things
simplified, the data is just ongoing and the results get put into
graphs/charts.

Your help is appreciated.

Simply change the range to what's appropriate in your situation. If you want
the entire column, then use $A$1:$A$65535 (ie, change 100 to 65535, or
whatever your last row is).

Regards,
Fred.

"Dvinechild" wrote in message
...
Thanks Mike, I'm working on this but want to know if I can alter the
equation
to read the entire column instead of seemingly stopping at row 100? I also
need to refer to another tab, do I enter that reference before the start
of
the absolutes?

"Mike H" wrote:

oops,

needs to be absolute

=COUNT(IF(INT((MONTH($A$1:$A$100)/4)+1)=ROW(A1),IF($A$1:$A$100<"",$A$1:$A$100)))

Mike

"Dvinechild" wrote:

Hola fellow Excel Kings & Queens~

Please let me know if there is a way to do the following:
Count the number of entries per quarter of data listed in a column.

I've thought about setting a < of entries falling between set date
criteria, but unsure how to do this. Column is currently formatted in
mm-dd-yy. I don't understand enough about converting date info so that
I
might get the infor I'm after, but I have a hunch that I might have to
go
down that road. :-)

Equation can be seperated into 4 seperate equations to keep things
simplified, the data is just ongoing and the results get put into
graphs/charts.

Your help is appreciated.

Thank you again.
The equation is not correctly stating the quantities however. The first qtr
is working fine,(36), but it is not counting the following qtrs correctly.
2nd qtr (dates fro 4/1 to 6/30), etc. are not being reflected accurately. My
sprdsht has 35 entries for 2nd qtr and using the equation, it's coming up
with 37.

Can you think of why? I've been trying all kinds of things but am not coming
up with the solution.



"Fred Smith" wrote:

Simply change the range to what's appropriate in your situation. If you want
the entire column, then use $A$1:$A$65535 (ie, change 100 to 65535, or
whatever your last row is).

Regards,
Fred.

"Dvinechild" wrote in message
...
Thanks Mike, I'm working on this but want to know if I can alter the
equation
to read the entire column instead of seemingly stopping at row 100? I also
need to refer to another tab, do I enter that reference before the start
of
the absolutes?

"Mike H" wrote:

oops,

needs to be absolute

=COUNT(IF(INT((MONTH($A$1:$A$100)/4)+1)=ROW(A1),IF($A$1:$A$100<"",$A$1:$A$100)))

Mike

"Dvinechild" wrote:

Hola fellow Excel Kings & Queens~

Please let me know if there is a way to do the following:
Count the number of entries per quarter of data listed in a column.

I've thought about setting a < of entries falling between set date
criteria, but unsure how to do this. Column is currently formatted in
mm-dd-yy. I don't understand enough about converting date info so that
I
might get the infor I'm after, but I have a hunch that I might have to
go
down that road. :-)

Equation can be seperated into 4 seperate equations to keep things
simplified, the data is just ongoing and the results get put into
graphs/charts.

Your help is appreciated.

How are you coming up with the formula for the 2nd quarter? You should be
dragging Mike's formula down one row to get the subsequent quarters. The 2nd
quarter formula should look like:
=COUNT(IF(INT((MONTH($A$1:$A$65535)/4)+1)=ROW(A2),IF($A$1:$A$65535<"",$A$1:$A$65535)) )

Regards,
Fred.

"Dvinechild" wrote in message
...
Thank you again.
The equation is not correctly stating the quantities however. The first
qtr
is working fine,(36), but it is not counting the following qtrs correctly.
2nd qtr (dates fro 4/1 to 6/30), etc. are not being reflected accurately.
My
sprdsht has 35 entries for 2nd qtr and using the equation, it's coming up
with 37.

Can you think of why? I've been trying all kinds of things but am not
coming
up with the solution.



"Fred Smith" wrote:

Simply change the range to what's appropriate in your situation. If you
want
the entire column, then use $A$1:$A$65535 (ie, change 100 to 65535, or
whatever your last row is).

Regards,
Fred.

"Dvinechild" wrote in message
...
Thanks Mike, I'm working on this but want to know if I can alter the
equation
to read the entire column instead of seemingly stopping at row 100? I
also
need to refer to another tab, do I enter that reference before the
start
of
the absolutes?

"Mike H" wrote:

oops,

needs to be absolute

=COUNT(IF(INT((MONTH($A$1:$A$100)/4)+1)=ROW(A1),IF($A$1:$A$100<"",$A$1:$A$100)))

Mike

"Dvinechild" wrote:

Hola fellow Excel Kings & Queens~

Please let me know if there is a way to do the following:
Count the number of entries per quarter of data listed in a
column.

I've thought about setting a < of entries falling between set date
criteria, but unsure how to do this. Column is currently formatted
in
mm-dd-yy. I don't understand enough about converting date info so
that
I
might get the infor I'm after, but I have a hunch that I might have
to
go
down that road. :-)

Equation can be seperated into 4 seperate equations to keep things
simplified, the data is just ongoing and the results get put into
graphs/charts.

Your help is appreciated.

Hi Fred,
I've done what both have you suggested, yet the count is not correct per
quarter. Would you be willing briefly look at my column of data? I have just
that info seperated out.

Kindly,
Justis



"Fred Smith" wrote:

How are you coming up with the formula for the 2nd quarter? You should be
dragging Mike's formula down one row to get the subsequent quarters. The 2nd
quarter formula should look like:
=COUNT(IF(INT((MONTH($A$1:$A$65535)/4)+1)=ROW(A2),IF($A$1:$A$65535<"",$A$1:$A$65535)) )

Regards,
Fred.

"Dvinechild" wrote in message
...
Thank you again.
The equation is not correctly stating the quantities however. The first
qtr
is working fine,(36), but it is not counting the following qtrs correctly.
2nd qtr (dates fro 4/1 to 6/30), etc. are not being reflected accurately.
My
sprdsht has 35 entries for 2nd qtr and using the equation, it's coming up
with 37.

Can you think of why? I've been trying all kinds of things but am not
coming
up with the solution.



"Fred Smith" wrote:

Simply change the range to what's appropriate in your situation. If you
want
the entire column, then use $A$1:$A$65535 (ie, change 100 to 65535, or
whatever your last row is).

Regards,
Fred.

"Dvinechild" wrote in message
...
Thanks Mike, I'm working on this but want to know if I can alter the
equation
to read the entire column instead of seemingly stopping at row 100? I
also
need to refer to another tab, do I enter that reference before the
start
of
the absolutes?

"Mike H" wrote:

oops,

needs to be absolute

=COUNT(IF(INT((MONTH($A$1:$A$100)/4)+1)=ROW(A1),IF($A$1:$A$100<"",$A$1:$A$100)))

Mike

"Dvinechild" wrote:

Hola fellow Excel Kings & Queens~

Please let me know if there is a way to do the following:
Count the number of entries per quarter of data listed in a
column.

I've thought about setting a < of entries falling between set date
criteria, but unsure how to do this. Column is currently formatted
in
mm-dd-yy. I don't understand enough about converting date info so
that
I
might get the infor I'm after, but I have a hunch that I might have
to
go
down that road. :-)

Equation can be seperated into 4 seperate equations to keep things
simplified, the data is just ongoing and the results get put into
graphs/charts.

Your help is appreciated.

Sure. Cut and paste it to a message in this group, or save your file to the
web, and let me know where it is.

Regards,
Fred.

"Dvinechild" wrote in message
...
Hi Fred,
I've done what both have you suggested, yet the count is not correct per
quarter. Would you be willing briefly look at my column of data? I have
just
that info seperated out.

Kindly,
Justis



"Fred Smith" wrote:

How are you coming up with the formula for the 2nd quarter? You should be
dragging Mike's formula down one row to get the subsequent quarters. The
2nd
quarter formula should look like:
=COUNT(IF(INT((MONTH($A$1:$A$65535)/4)+1)=ROW(A2),IF($A$1:$A$65535<"",$A$1:$A$65535)) )

Regards,
Fred.

"Dvinechild" wrote in message
...
Thank you again.
The equation is not correctly stating the quantities however. The first
qtr
is working fine,(36), but it is not counting the following qtrs
correctly.
2nd qtr (dates fro 4/1 to 6/30), etc. are not being reflected
accurately.
My
sprdsht has 35 entries for 2nd qtr and using the equation, it's coming
up
with 37.

Can you think of why? I've been trying all kinds of things but am not
coming
up with the solution.



"Fred Smith" wrote:

Simply change the range to what's appropriate in your situation. If
you
want
the entire column, then use $A$1:$A$65535 (ie, change 100 to 65535, or
whatever your last row is).

Regards,
Fred.

"Dvinechild" wrote in message
...
Thanks Mike, I'm working on this but want to know if I can alter the
equation
to read the entire column instead of seemingly stopping at row 100?
I
also
need to refer to another tab, do I enter that reference before the
start
of
the absolutes?

"Mike H" wrote:

oops,

needs to be absolute

=COUNT(IF(INT((MONTH($A$1:$A$100)/4)+1)=ROW(A1),IF($A$1:$A$100<"",$A$1:$A$100)))

Mike

"Dvinechild" wrote:

Hola fellow Excel Kings & Queens~

Please let me know if there is a way to do the following:
Count the number of entries per quarter of data listed in a
column.

I've thought about setting a < of entries falling between set
date
criteria, but unsure how to do this. Column is currently
formatted
in
mm-dd-yy. I don't understand enough about converting date info so
that
I
might get the infor I'm after, but I have a hunch that I might
have
to
go
down that road. :-)

Equation can be seperated into 4 seperate equations to keep
things
simplified, the data is just ongoing and the results get put into
graphs/charts.

Your help is appreciated.