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-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org
