IF with nested AND
Oh, ok.
See Bob's solution, need to ensure the times in quotes are treated as
numbers, and the easy way to do it is with the -- in front of the quotes.
In regards to your other formula. You have realized that it is an array, and
you still have to sum the array formula.
=SUM(SUMIF(Range1,RangeTest,Range2))
Since this is an array formula, remember to press CTRL+SHIFT+ENTER to enter
the formula.
--
John C
"Joco" wrote:
Hi John thanks for your reply
I have changed the one difference in the formulay, but irrespective of the
time the answer is always shown as 3
I was actually looking to highlite thoose after 18:00 and before 06:00
Joco (London)
"John C" wrote:
I think your first formula is in error. You are in effect only looking for
any value in C1575 that is greater than "18:00:00"
your AND portion should be:
AND("06:00:00"<C1575,C1575<"18:00:00")
if you are wanting to include only the times between 6am and 6pm (non
inclusive).
--
John C
"Joco" wrote:
Hi,
Can you please tell me how to put a symbol on a line, if a time is before or
after a specified time. I have tried the following which is a valid formula,
but does not give me the correct answer.
=IF(AND("06:00:00"<C1575,C1575"18:00:00"),5,3) (the relevant field is
formatted to monotype sorts to give either a tick or a cross)
I am also trying to produce an IF formula that looks at a block of cells, I
have tried the following, which only works for the first cell in the block
=SUMIF(E10:E1600,E1609:E1615,(K10:K1600))
The E1609:E1615 contains text names
Any help would be really appreciated, thanks
Joco (London)
|