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)

"Jack" wrote in message
...
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

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

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

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

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

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.

--
Regards,
Tom Ogilvy


"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

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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"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

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 . . .

--
Regards,
Tom Ogilvy


"Jack" wrote:

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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"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