if and countif

[lee6553]

Thanks for the help. It worked but I didn't think it through enough an
now I have additional problem that I hope you can figure out.

This is what I am trying to accomplish. Count only if column A has a
I and if column K has a time value greater than or equal to 0:10 bu
less than 0:20.

I tried adding to the formula that you provided but it doesn't seem t
count right.

=SUMPRODUCT(--('2000'!A2:A1399="I"),--('2000'!K2:K1399>=TIME(0,10,0))--('2000'!K2:K1399<=TIME(0,19,0)))

I have to do this for 10 minute increaments until I reach 1 hour an
then anything over an hour in K and A=I.

Thanks again.
Le

 

[Biff]

Hi!

Looks like you're just missing a comma after the second
array.

You really don't need the TIME functions either:

=SUMPRODUCT(--('2000'!A2:A1399="I"),--('2000'!
K2:K1399>=10/1440),--('2000'!K2:K1399<=19/1440))

Biff

 

[Bob Phillips]

You don't, but it is more readable in time format, and better (IMO) to test
less than next time boundary

=SUMPRODUCT(--('2000'!A2:A1399="I"),--('2000'!K2:K1399>=--("00:10:00")),--('
2000'!K2:K1399<--("00:20:00")))

but of course it is moire flexible to store the time boundaries in cells