sumif? countif? i'm stuck

[orsm6]

hi all.

is there a way to perform the following?

in range sheet 2 H22:H39 look for value Sheet3 B3 and if that value is between sheet 3 L11 and M11 return 1, otherwise return 0.
also, the values in these ranges are times formatted as h:mm


any help would be great.
thank you.

EDIT..... now I think about this, the look up will never work lol. the value to find will never be in the range to look up.

 

[etaf]

i assume no longer needed
but, if so
i'm not sure why you mention 2 sheets sheet2!h22:h39 and sheet3!L11:M11
the value is sheet3!B3

look for that value where ?

 

[snjpverma]

Try this

=--(Sheet3!B3<(MAX(Sheet3!L11,Sheet3!M11))*(Sheet3!B3>(MIN(Sheet3!L11,Sheet3!M11))))