It sounded like he may not know

=sumproduct(--(Sheet1!a1:a10="F"),--(Sheet1!b1:b10<time(2,0,0)))


if the data is on a sheet with a tab name/Sheet Name of Sheet1 and the
formula is on another sheet.


if the sheet name has a space in it, then it would be

=sumproduct(--('My Data'!a1:a10="F"),--('My Data'!b1:b10<time(2,0,0)))

as an example.



--
Regards,
Tom Ogilvy



"Dave Peterson" wrote:

Post the formula you used.

I did have a typo in my suggestion--it was missing the final ")". But excel
corrected that when I hit enter.

Jack wrote:

Thank you very much Dave and Bob - much appreciated.

However I continue to get errors when I use both your formulas.

The COUNTIF formula is typed into a 'summary' sheet, with the source data
coming from another sheet in the same workbook.

Any additional help would be appreciated.
Thanks

"Dave Peterson" wrote:

A worksheet formula?

=sumproduct(--(a1:a10="F"),--(b1:b10<time(2,0,0))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



Jack wrote:

Hi,

I am trying to filter a spreadsheet which lists in one column job type, and
in the other column response time (total duration in hh:mm:ss). I have been
trying to figure out a COUNTIF query which would allow me to obtain the
number of jobs which have a job type of 'F' for 'Fault', and which have a
response time of less than 2 hours.

If anyone can help me with this, that would be greatly appreciated.
Thanks

--

Dave Peterson


--

Dave Peterson