COUNTIF Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=SUMPRODUCT(--(A2:A100="F"),--(B2:B100<--"2:00:00"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
You might also look at a pivot table. You could add a dummy column to mark
all records that have a response time of less than two hours - maybe
categorize all.

The pivot table would summarize at almost any level and combination you want.

data=>Pivot Table

Information on Pivot Tables here
Debra Dalgleish
http://www.contextures.com/tiptech.html

If you don't use it for this, and you are not familiar with them, you should
- they are one of the most powerful features of Excel for analyzing data.
 
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
 
Thanks Tom,

The reason why I am using COUNTIF is to show how many records (represented
as a figure - not in a list) have a response time of less than two hours.
E.g. - 5 'Fault' type jobs have a response time of less than 2 hours.

Thanks
 
If that is the only thing you want to know, then you are correct that a pivot
table would not be appropriate. If you are going to get that statistic and
then get other statistics, then . . .
 
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.
 
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.
 
Back
Top