software

I have a 2500 line document with different years indicated in column D. How
would I use the count (??) function to count the number of occurances for
1998, 1999, 2000, etc?

The help function isn't very much help to me.

Thank you.


=COUNTIF(D12500=1999) - use the year you want

Ed--
EdMac
------------------------------------------------------------------------
EdMac's Profile: www.excelforum.com/member.php...oamp;userid=30736
View this thread: www.excelforum.com/showthread...hreadid=504479Type

=COUNTIF(D12500,1999)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;EdMacquot; gt; wrote in
message ...
gt;
gt; =COUNTIF(D12500=1999) - use the year you want
gt;
gt; Ed
gt;
gt;
gt; --
gt; EdMac
gt; ------------------------------------------------------------------------
gt; EdMac's Profile:
www.excelforum.com/member.php...oamp;userid=30736
gt; View this thread: www.excelforum.com/showthread...hreadid=504479
gt;
*sigh*

I regretted to provide information that the dates are formatted as
dd/mm/yyyy, so the formulas provided to me will not work.

Is there another way this can be done?

Sorry for the confusion.

Thank you.quot;Bob Phillipsquot; wrote:

gt; Type
gt;
gt; =COUNTIF(D12500,1999)
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;EdMacquot; gt; wrote in
gt; message ...
gt; gt;
gt; gt; =COUNTIF(D12500=1999) - use the year you want
gt; gt;
gt; gt; Ed
gt; gt;
gt; gt;
gt; gt; --
gt; gt; EdMac
gt; gt; ------------------------------------------------------------------------
gt; gt; EdMac's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30736
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=504479
gt; gt;
gt;
gt;
gt;

Try:
=SUMPRODUCT(--(YEAR(D12500) = 1999))
--
Kevin Vaughnquot;MABquot; wrote:

gt; *sigh*
gt;
gt; I regretted to provide information that the dates are formatted as
gt; dd/mm/yyyy, so the formulas provided to me will not work.
gt;
gt; Is there another way this can be done?
gt;
gt; Sorry for the confusion.
gt;
gt; Thank you.
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Type
gt; gt;
gt; gt; =COUNTIF(D12500,1999)
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from the email address if mailing direct)
gt; gt;
gt; gt; quot;EdMacquot; gt; wrote in
gt; gt; message ...
gt; gt; gt;
gt; gt; gt; =COUNTIF(D12500=1999) - use the year you want
gt; gt; gt;
gt; gt; gt; Ed
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; EdMac
gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; EdMac's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=30736
gt; gt; gt; View this thread: www.excelforum.com/showthread...hreadid=504479
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;

This gives me a #VALUE! error message.

Is there anything else I can try?

Thank you,

MAB

quot;Kevin Vaughnquot; wrote:

gt; Try:
gt; =SUMPRODUCT(--(YEAR(D12500) = 1999))
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;MABquot; wrote:
gt;
gt; gt; *sigh*
gt; gt;
gt; gt; I regretted to provide information that the dates are formatted as
gt; gt; dd/mm/yyyy, so the formulas provided to me will not work.
gt; gt;
gt; gt; Is there another way this can be done?
gt; gt;
gt; gt; Sorry for the confusion.
gt; gt;
gt; gt; Thank you.
gt; gt;
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; Type
gt; gt; gt;
gt; gt; gt; =COUNTIF(D12500,1999)
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from the email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;EdMacquot; gt; wrote in
gt; gt; gt; message ...
gt; gt; gt; gt;
gt; gt; gt; gt; =COUNTIF(D12500=1999) - use the year you want
gt; gt; gt; gt;
gt; gt; gt; gt; Ed
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; EdMac
gt; gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; EdMac's Profile:
gt; gt; gt; www.excelforum.com/member.php...oamp;userid=30736
gt; gt; gt; gt; View this thread: www.excelforum.com/showthread...hreadid=504479
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;

What is in that range other than dates? I tried deleting a date to see if it
was being caused by blanks. That did not do anything. But when I inserted a
space in a cell I got the #value error. I am sure that other data could
cause that error. Like text (you can't perform the year function on text,
only on dates.)
--
Kevin Vaughnquot;MABquot; wrote:

gt; This gives me a #VALUE! error message.
gt;
gt; Is there anything else I can try?
gt;
gt; Thank you,
gt;
gt; MAB
gt;
gt; quot;Kevin Vaughnquot; wrote:
gt;
gt; gt; Try:
gt; gt; =SUMPRODUCT(--(YEAR(D12500) = 1999))
gt; gt; --
gt; gt; Kevin Vaughn
gt; gt;
gt; gt;
gt; gt; quot;MABquot; wrote:
gt; gt;
gt; gt; gt; *sigh*
gt; gt; gt;
gt; gt; gt; I regretted to provide information that the dates are formatted as
gt; gt; gt; dd/mm/yyyy, so the formulas provided to me will not work.
gt; gt; gt;
gt; gt; gt; Is there another way this can be done?
gt; gt; gt;
gt; gt; gt; Sorry for the confusion.
gt; gt; gt;
gt; gt; gt; Thank you.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Type
gt; gt; gt; gt;
gt; gt; gt; gt; =COUNTIF(D12500,1999)
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove nothere from the email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;EdMacquot; gt; wrote in
gt; gt; gt; gt; message ...
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =COUNTIF(D12500=1999) - use the year you want
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Ed
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; EdMac
gt; gt; gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; EdMac's Profile:
gt; gt; gt; gt; www.excelforum.com/member.php...oamp;userid=30736
gt; gt; gt; gt; gt; View this thread: www.excelforum.com/showthread...hreadid=504479
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;

Hmm, I don't know. I just formatted all the cells where the dates are to
Date format (mm/dd/yyyy). I even did a text to columns on that column, yet I
still get the #Value! error.

Any other thoughts?

Thanks for all your help so far. :-)quot;Kevin Vaughnquot; wrote:

gt; What is in that range other than dates? I tried deleting a date to see if it
gt; was being caused by blanks. That did not do anything. But when I inserted a
gt; space in a cell I got the #value error. I am sure that other data could
gt; cause that error. Like text (you can't perform the year function on text,
gt; only on dates.)
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;MABquot; wrote:
gt;
gt; gt; This gives me a #VALUE! error message.
gt; gt;
gt; gt; Is there anything else I can try?
gt; gt;
gt; gt; Thank you,
gt; gt;
gt; gt; MAB
gt; gt;
gt; gt; quot;Kevin Vaughnquot; wrote:
gt; gt;
gt; gt; gt; Try:
gt; gt; gt; =SUMPRODUCT(--(YEAR(D12500) = 1999))
gt; gt; gt; --
gt; gt; gt; Kevin Vaughn
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;MABquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; *sigh*
gt; gt; gt; gt;
gt; gt; gt; gt; I regretted to provide information that the dates are formatted as
gt; gt; gt; gt; dd/mm/yyyy, so the formulas provided to me will not work.
gt; gt; gt; gt;
gt; gt; gt; gt; Is there another way this can be done?
gt; gt; gt; gt;
gt; gt; gt; gt; Sorry for the confusion.
gt; gt; gt; gt;
gt; gt; gt; gt; Thank you.
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Type
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =COUNTIF(D12500,1999)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; HTH
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; (remove nothere from the email address if mailing direct)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;EdMacquot; gt; wrote in
gt; gt; gt; gt; gt; message ...
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; =COUNTIF(D12500=1999) - use the year you want
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Ed
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; EdMac
gt; gt; gt; gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; gt; EdMac's Profile:
gt; gt; gt; gt; gt; www.excelforum.com/member.php...oamp;userid=30736
gt; gt; gt; gt; gt; gt; View this thread: www.excelforum.com/showthread...hreadid=504479
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;

Ok, but what else is in that range? For instance D1 might be a heading.
Also, does it work if you do it on a small range? For instance,

=SUMPRODUCT(--(YEAR(D3210) = 1999))

If it works on the smaller range, you could try it on progressively larger
ranges until you get an error and thus locate the source of the problem
(assuming the formula works for you at all. It is working for me, of course.)
--
Kevin Vaughnquot;MABquot; wrote:

gt; Hmm, I don't know. I just formatted all the cells where the dates are to
gt; Date format (mm/dd/yyyy). I even did a text to columns on that column, yet I
gt; still get the #Value! error.
gt;
gt; Any other thoughts?
gt;
gt; Thanks for all your help so far. :-)
gt;
gt;
gt; quot;Kevin Vaughnquot; wrote:
gt;
gt; gt; What is in that range other than dates? I tried deleting a date to see if it
gt; gt; was being caused by blanks. That did not do anything. But when I inserted a
gt; gt; space in a cell I got the #value error. I am sure that other data could
gt; gt; cause that error. Like text (you can't perform the year function on text,
gt; gt; only on dates.)
gt; gt; --
gt; gt; Kevin Vaughn
gt; gt;
gt; gt;
gt; gt; quot;MABquot; wrote:
gt; gt;
gt; gt; gt; This gives me a #VALUE! error message.
gt; gt; gt;
gt; gt; gt; Is there anything else I can try?
gt; gt; gt;
gt; gt; gt; Thank you,
gt; gt; gt;
gt; gt; gt; MAB
gt; gt; gt;
gt; gt; gt; quot;Kevin Vaughnquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Try:
gt; gt; gt; gt; =SUMPRODUCT(--(YEAR(D12500) = 1999))
gt; gt; gt; gt; --
gt; gt; gt; gt; Kevin Vaughn
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;MABquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; *sigh*
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I regretted to provide information that the dates are formatted as
gt; gt; gt; gt; gt; dd/mm/yyyy, so the formulas provided to me will not work.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Is there another way this can be done?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Sorry for the confusion.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thank you.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Type
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; =COUNTIF(D12500,1999)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; HTH
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; (remove nothere from the email address if mailing direct)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;EdMacquot; gt; wrote in
gt; gt; gt; gt; gt; gt; message ...
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; =COUNTIF(D12500=1999) - use the year you want
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Ed
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; gt; EdMac
gt; gt; gt; gt; gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; gt; gt; EdMac's Profile:
gt; gt; gt; gt; gt; gt; www.excelforum.com/member.php...oamp;userid=30736
gt; gt; gt; gt; gt; gt; gt; View this thread: www.excelforum.com/showthread...hreadid=504479
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;

LOL!

See, that's why I come here for help. I was including all of column D in
the formula instead of just including the area where the valid data is.

I got it to work when I used it correctly.

Thanks for all your input and patience.

MAB

quot;Kevin Vaughnquot; wrote:

gt; Ok, but what else is in that range? For instance D1 might be a heading.
gt; Also, does it work if you do it on a small range? For instance,
gt;
gt; =SUMPRODUCT(--(YEAR(D3210) = 1999))
gt;
gt; If it works on the smaller range, you could try it on progressively larger
gt; ranges until you get an error and thus locate the source of the problem
gt; (assuming the formula works for you at all. It is working for me, of course.)
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;MABquot; wrote:
gt;
gt; gt; Hmm, I don't know. I just formatted all the cells where the dates are to
gt; gt; Date format (mm/dd/yyyy). I even did a text to columns on that column, yet I
gt; gt; still get the #Value! error.
gt; gt;
gt; gt; Any other thoughts?
gt; gt;
gt; gt; Thanks for all your help so far. :-)
gt; gt;
gt; gt;
gt; gt; quot;Kevin Vaughnquot; wrote:
gt; gt;
gt; gt; gt; What is in that range other than dates? I tried deleting a date to see if it
gt; gt; gt; was being caused by blanks. That did not do anything. But when I inserted a
gt; gt; gt; space in a cell I got the #value error. I am sure that other data could
gt; gt; gt; cause that error. Like text (you can't perform the year function on text,
gt; gt; gt; only on dates.)
gt; gt; gt; --
gt; gt; gt; Kevin Vaughn
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;MABquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; This gives me a #VALUE! error message.
gt; gt; gt; gt;
gt; gt; gt; gt; Is there anything else I can try?
gt; gt; gt; gt;
gt; gt; gt; gt; Thank you,
gt; gt; gt; gt;
gt; gt; gt; gt; MAB
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Kevin Vaughnquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Try:
gt; gt; gt; gt; gt; =SUMPRODUCT(--(YEAR(D12500) = 1999))
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; Kevin Vaughn
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;MABquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; *sigh*
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I regretted to provide information that the dates are formatted as
gt; gt; gt; gt; gt; gt; dd/mm/yyyy, so the formulas provided to me will not work.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Is there another way this can be done?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Sorry for the confusion.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thank you.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Type
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; =COUNTIF(D12500,1999)
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; HTH
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; (remove nothere from the email address if mailing direct)
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; quot;EdMacquot; gt; wrote in
gt; gt; gt; gt; gt; gt; gt; message ...
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; =COUNTIF(D12500=1999) - use the year you want
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; Ed
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; gt; gt; EdMac
gt; gt; gt; gt; gt; gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; gt; gt; gt; EdMac's Profile:
gt; gt; gt; gt; gt; gt; gt; www.excelforum.com/member.php...oamp;userid=30736
gt; gt; gt; gt; gt; gt; gt; gt; View this thread: www.excelforum.com/showthread...hreadid=504479
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt;