Re: IF with nested AND

Even array entered it doesn't work. My SP formula is one way.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"John C" <johnc@stateofdenial> wrote in message
news:3267393D-52B9-493F-A89B-B76B5B565D37@xxxxxxxxxxxxxxxx
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)


.

Relevant Pages

  • Re: IF with nested AND
    ... Worked just fine for me with array entered. ... John C ... "Joco" wrote: ... which only works for the first cell in the ...
    (microsoft.public.excel.misc)
  • or(exact)) function does not work
    ... I am trying to compare a list of words to an array of words. ... john to great ... searching for is not the first cell in the array..... ...
    (microsoft.public.excel.programming)
  • Re: my doesnt totally isolate
    ... John> Hmmm.... ... So how am I supposed to distinguish between an array ... what control you see is an undocumented side effect and may go ... allocation and undef. ...
    (comp.lang.perl.misc)
  • Re: script to find most common last names in a file
    ... John W. Krahn wrote: ... > splitreturns a list in list context so you need list context on the ... > splitwill store its results in the @_ array. ... >> # then creat a hash table of first and last names, ...
    (perl.beginners)
  • Re: Second minimum
    ... John D'Errico wrote: ... > Ind is the index into the row of the sorted array which ... > Now use the tags array to back out where it came from ... > preferably the same, cat. ...
    (comp.soft-sys.matlab)
Loading