Re: Using Countif for Date Matches

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: RagDyer (ragdyer_at_cutoutmsn.com)
Date: 12/29/04 

Date: Wed, 29 Dec 2004 13:59:59 -0800

Thanks for the feed-back.

And I'll bet that *THAT* WAS* the source of the problem, the header being
*TEXT*.
If the header was a "true" date, formatted to display whatever you wanted to
see, the formula would have worked! 

-- 
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------
"JBoulton" <JBoulton@discussions.microsoft.com> wrote in message
news:2AFD24DA-9380-4591-B6AF-250FE1FAEAAC@microsoft.com...
Rag,
You're right in that those errors *would* cause my problem, but it turns out
that's not the source of the problem.  A1 contains a heading (date.)  When I
changed the array to $a$2 the error disappeared.
Thanks so much for your helpful attention to my problem.  My final formula,
where I was truly heading is:
=SUMPRODUCT(--(MONTH(A$2:$A183)=MONTH($A183)),C$2:$C183)
This gives me the running total by month.  Now it works as it should.
Thanks, again.
"RagDyer" wrote:
> Couple of ways I could duplicate the VALUE! error with correctly formatted
> date data, was to put a <Space> in front of the date, and/or have *alpha*
> text somewhere within the range.
> Numeric text is accepted.
> Even a space entered in front of a *single* date in the entire range
> produced the #VALUE! error, even if the date was not one of the ones that
> met the criteria.
>
> Any possibility of that?
>
> Try this formula:
>
> =SUMPRODUCT(--(YEAR(TRIM(A$1:A182))=2004))
>
> This will take care of any cells that may contain a leading space.
> However, the caveat here, is that with this TRIM() inserted, you *CANNOT*
> have any empty cells in the range (A1:A182).
>
> Also, what happens when you try the second and third formulas of Jason?
> Either of those should work with text or spaces.
> -- 
>
> HTH,
>
> RD
> --------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit!
> -------------------------------------------------------------------
>
> "JBoulton" <JBoulton@discussions.microsoft.com> wrote in message
> news:6978280C-C70A-4DCD-9F21-8DBE37B894D0@microsoft.com...
> Rag,
>
> The daya is keyed.  I can sort the data and it appears correct.  If there
> were something amiss, the errors would be at the top or bottom, I think.
>
> "RagDyeR" wrote:
>
> > I have to agree that the problem is probably your data in the search
> column.
> >
> > Exactly how is Column A populated?
> >
> > Keyed in ... result of formula ... import from other source?
> > -- 
> >
> > Regards,
> >
> > RD
> > --------------------------------------------------------------------
> > Please keep all correspondence within the Group, so all may benefit !
> > --------------------------------------------------------------------
> >
> > "JBoulton" <JBoulton@discussions.microsoft.com> wrote in message
> > news:1B0EFD82-92DE-487D-A81A-018183008703@microsoft.com...
> > tj,
> >
> > That's an interesting idea, but doesn't apply here.  My dates are
> mm/dd/yyyy
> > both in the spread*** and in the control pannel.
> >
> >
> >
> > "tjtjjtjt" wrote:
> >
> > > How are you typing your dates compared to your system settings?
> > >
> > > I'm using US dates: 12/26/2004 means December 26th of 2004. If I type
> > > 26/12/2004 in my range, I get #VALUE.
> > >
> > > I copied and pasted your formula, and it worked fine. I suspect you
are
> > > typing your dates in a way your operating system isn't recognizing as
a
> > date.
> > >
> > > (If your) In Windows, go into the Control Panel and find the Regional
> > > Settings. Do the settings for your Date Formats look the same as how
you
> > > typed Dtes in Excel? If not, that is the problem.
> > >
> > > tj
> > >
> > > "JBoulton" wrote:
> > >
> > > > tj
> > > >
> > > > That looks so simple, but I get !VALUE# using this formula:
> > > >
> > > > =SUMPRODUCT(--(YEAR(A$1:A182)=2004))
> > > >
> > > > Col A is all dates.  What's wrong?
> > > >
> > > > TIA
> > > >
> > > > "tjtjjtjt" wrote:
> > > >
> > > > > Perhaps something like:
> > > > > =SUMPRODUCT(--(YEAR(SalesForecast!D1:D4)=2004))
> > > > >
> > > > > For an explanation of what is going on, see:
> > > > > http://www.xldynamic.com/source/xld.SUMPRODUCT.html
> > > > >
> > > > > tj
> > > > >
> > > > > "Raymond Gallegos" wrote:
> > > > >
> > > > > > Hello.  I am trying to count the number of sales that occurred
> > > > > > in 2004 by using the following Countif formula
> > > > > > =COUNTIF(SalesForecast!D1:D4,year=2004), but all I get is 0,
when
> > > > > > I thought I would get 1, which represented the sale for 2004.
> > > > > > Can anyone help?
> > > > > >
> > > > > > Data
> > > > > > 01/01/2003
> > > > > > 03/15/2003
> > > > > > 06/01/2004
> > > > > > 09/30/2005
> >
> >
> >
>
>
Quantcast