Prev: Count how many times conditional formatted cells are positive
Next: SUMIF function not calculating from closed worksheet
From: LUSN on 3 Mar 2010 15:20
I want to count all the entries in column N that occurred between a range of
dates specified in the other criteria. The dates are obviously in column B.

I use this currently-
COUNTIFS(Data!N:N,"=Option",Data!B:B,">=1/1/2009",Data!B:B,"<=2/28/2009")

However, I use this similar function in multiple cells, all counting
different attributes. Rather than going into each cell and changing the
search dates, I wanted a criteria to be a variable that can be entered into a
different set of cells... like this.

COUNTIFS(Data!N:N,"=Option",Data!B:B,">=A4",Data!B:B,"<=2/28/2009")

With A4 being a cell in which to enter the date. However, it doesn't work! I
get zero as a result every time. Is there a particular format i need to use
to indicate a date?


From: Fred Smith on 3 Mar 2010 15:33
Do your dates and comparisons this way:
COUNTIFS(Data!N:N,"=Option",Data!B:B,">="&A4,Data!B:B,"<="&DATE(2009,2,28))

Regards,
Fred

"LUSN" <LUSN(a)discussions.microsoft.com> wrote in message
news:9A92537E-F5DF-4AAC-A593-F738A328EED6(a)microsoft.com...
>I want to count all the entries in column N that occurred between a range
>of
> dates specified in the other criteria. The dates are obviously in column
> B.
>
> I use this currently-
> COUNTIFS(Data!N:N,"=Option",Data!B:B,">=1/1/2009",Data!B:B,"<=2/28/2009")
>
> However, I use this similar function in multiple cells, all counting
> different attributes. Rather than going into each cell and changing the
> search dates, I wanted a criteria to be a variable that can be entered
> into a
> different set of cells... like this.
>
> COUNTIFS(Data!N:N,"=Option",Data!B:B,">=A4",Data!B:B,"<=2/28/2009")
>
> With A4 being a cell in which to enter the date. However, it doesn't work!
> I
> get zero as a result every time. Is there a particular format i need to
> use
> to indicate a date?
>
>

 | 
Pages: 1
Prev: Count how many times conditional formatted cells are positive
Next: SUMIF function not calculating from closed worksheet