Count If question

[Guest]

I have a spread sheet that I need to count if -

If Column T is equal to a certain name (Davis) then I want to count the
times that are in Column AR that are less than or equal to 00:30.

Column T has numerous last names, so I need to specify who I want the times
on.

Thanks for any help.

 

[JE McGimpsey]

One way:

Assume you specify the name in cell S1. Then

=SUMPRODUCT(--(T1:T1000=S1), --(AR1:AR1000<=TIME(0,30,0)))

or, equivalently:

=SUMPRODUCT(--(T1:T1000=S1), --(AR1:AR1000<=1/48))

 

[Guest]

try the sumproduct() function
= sumproduct(--(T1:T1000="Davis"),--(timevalue(AR1:AR1000)<=timevalue(00:30)))
the --( changes the logical true false to a 1,0 numeric
the arrays in each section must be the same size and can not be the
shorthand for entire columns

 

[Bob Phillips]

=SUMPRODUCT(--(T1:T100="Davis"),--(AR1:AR100<=TIME(0,30,0)),AR1:AR100)

format the cell as [hh]:mm to cater for more than 24 hours

--

HTH

RP
(remove nothere from the email address if mailing direct)