You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by DaveInDenver <dw...@hotmail.com> on 2012/12/07 09:20:45 UTC
Re: countif on multiple items - date test
Good info here.
How can I modify the formula to test for a date?
If Column A were dates for example.
A1 = 08/01/12
A3 = 08/15/12
A9 = 09/01/12
How do I select for Aug 2012?
Thanks.
--
View this message in context: http://openoffice.2283327.n4.nabble.com/countif-on-multiple-items-tp4332335p4634206.html
Sent from the Users mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: countif on multiple items - date test
Posted by johnny smith <ka...@krovatka.su>.
On Fri, 07 Dec 2012 08:20:45 -0000, DaveInDenver <dw...@hotmail.com> wrote:
> How can I modify the formula to test for a date?
>
> If Column A were dates for example.
> A1 = 08/01/12
> A3 = 08/15/12
> A9 = 09/01/12
>
> How do I select for Aug 2012?
i've found 2 ways, both not ideal. the first is:
B1 = 08/01/12
B2 = 09/01/12
B3 = COUNTIF(A1:A9;">="&B1)-COUNTIF(A1:A9;">="&B2)
--which looks a bit like a workaround, as i was not able to find a way to apply a regular AND statement. however, this one is the most compatible, since it handles different date formats automatically.
the second option is to use wildcards:
B1 = COUNTIF(A1:A9;"08/[0-9]{2}/12")
or
B1 = COUNTIF(A1:A9;"August [0-9]{1,2}, 2012")
--which depends greatly on the locale settings and thus lacks compatibility when loading on a different machine.
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: countif on multiple items - date test
Posted by Brian Barker <b....@btinternet.com>.
At 00:20 07/12/2012 -0800, Dave Kerschen wrote:
>countif on multiple items - date test
>How can I modify the formula to test for a date?
>
>If Column A were dates for example.
>A1 = 08/01/12
>A3 = 08/15/12
>A9 = 09/01/12
>
>How do I select for Aug 2012?
This appears to work:
=SUMPRODUCT(MONTH(A1:A99)=8;YEAR(A1:A99)=2012)
MONTH(A1:A99)=8 is an array of logical values indicating whether the
month of each date is August and YEAR(A1:A99)=2012 similarly an array
of logical values indicating whether the year is 2012. When
corresponding members of the arrays are multiplied (the "product"
bit), these logical values are ANDed; the resulting TRUE values are
then summed, which has the effect of counting them. This relies on
the fact that logical values can be (mis-)interpreted as numbers,
with TRUE being 1 and FALSE being 0.
Note that if the dates are all known to be in 2012, the simpler formula
=SUMPRODUCT(MONTH(A1:A99)=8)
would also work.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org