Formula help - please !
=SUMPRODUCT(--($A$1:$A$100=--"2006-01-01"),--($B$1:$B$100<=--"12:00"))
and
=SUMPRODUCT(--($A$1:$A$100=--"2006-01-01"),--($B$1:$B$100--"12:00"))
but better to put the date and time in a cell and test against that
=SUMPRODUCT(--($A$1:$A$100=C1),--($B$1:$B$100<=C2))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Anthony" wrote in message
...
Hi and Happy New Year to all
I need a formula (and don't know where to start) for the following
In a worksheet I have a list of dates in column A and times in column B, I
want a formula to count each time a certain date and time before 12noon
appear in columns A and B, and likewise the amount of times a certain date
and time appear after 12noon.
eg, basic worksheet
A B
1Jan 11:23
1Jan 13:34
2Jan 09:10
2Jan 11:34
2Jan 15:43
so the result for 01Jan before 1200 would be 1, the result for 01Jan after
1200 would also be 1, the result for 02Jan before 1200 would be 2, and so
on.
I think think this could be solved using 'arrays' in the =SUMPRODUCT
formula
but I don't know how to write the formula
Appreciate any help
Kind regards
Anthony
|