CountIf question

[jlancton]

I've got a spreadsheet for call bell response times.

There's a column for time the bell was pulled, time answered, and a column that has the calculated difference.

I used a countif to count all the calls that were responded to in more than five minutes. That was no problem. =countif(e5:55,">0:05")

I also need to count the ones responded to in less than five minutes. I tried the same countif, only reversing the > to <, but I didn't get the correct results. It gave me everything less than 5, including all the ones with 'zero' minutes because there was nothing there.

How can I tell countif to count everything below 5 and greater than zero?

I tried this:

=countif(e5:e55,and(">0:00","<0:05"))

but that returns nothing, no error, but no count either.

Any help will be greatly appreciated.

Thanks,

-Jeff

 

[GlennUK]

Isn't it:

=countif(e5:55,">0:00")-countif(e5:55,">0:05")

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[jlancton]

Thank you Glenn!

With a few extra ()'s that did work.

Is there no way to do it with a single countif?

-Jeff

 

[AnotherHiggins]

->With a few extra ()'s that did work.
Why extra parentheses? What did you come up with? Other than changing the range to E5:[!]E[/!]55 it should have worked just fine. Do be sure to change the format of the cell containing the formula to General.

->Is there no way to do it with a single countif?
Nope. Glenn's suggestion is how it's done.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[jlancton]

Glenn's formula as copied gave an error.

This worked:

=((COUNTIF(E5:E55,">0"))-(COUNTIF(E5:E55,">0:05")))

Thanks!!

-Jeff

 

[AnotherHiggins]

Glad you got it working.

Just for the sake of elegance, I'll offer the following....

You can get rid of 3 pairs of parentheses. Use this:

=COUNTIF(E5:E55,">0")-COUNTIF(E5:E55,">0:05")

Make sure to format the cell as general (Format > Cells > Number > General) so it doesn't return the answer in time format.

Like I said, Glenn's formula will error if copied 'n' pasted because he left out the second "E" in each range. Other than that, his formula is the way to go.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[GlennUK]

Sorry about the error. I just typed the formula into the reply without testing, as it was going home time here.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[onedtent]

Hi.

You can't do it with a single countif, but you can with sumproduct. Since five minutes works out to be 0.0034722 of the full day (that is 5/1440) the formula below should give you the result you desire.

=SUMPRODUCT((E5:E55<0.0034723)*(E5:E55>0))

 

[AnotherHiggins]

Good approach, onedtent. But Excel doesn't calculate 5 minutes as 0.0034723 but rather 0.003472222 (repeating). So using the formula you posted would count all values of 5 minutes, not just times less than 5 minutes.

To avoid this (and make the formula easier to read later on) I would suggest you allow Excel to do the conversion in the formula, like this:
[tab][COLOR=blue white]=SUMPRODUCT((E5:E55<[red]VALUE("0:05")[/red]) * (E5:E55>0))[/color]

Also, looking back I see the Original Post contained the typo of leaving out the second "E" in the range.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

...or use a cell value (I ususally NAME the range, something like Interval in this instance)
[tt][highlight yellow]
=SUMPRODUCT((E5:E55<Interval) * (E5:E55>0))[/highlight]
[/tt]
Tha way a quick change of a VALUE in a cell can change all formulas using it.

Skip,

_[red][/red]

 

[kjv1611]

Also, you could do this as an array formula:

[b][BLUE]{[/BLUE][/b]=SUM((E5:E55<VALUE("0:05")) * (E5:E55>0))[b][BLUE]}[/BLUE][/b]

[BLUE]**Note the brackets are added after you hit <Ctrl> + <Enter> on your keyboard - you do not manually type them in.**[/BLUE]

http://www.cpearson.com

Has tons of examples for array formulas, and probably these other scenerios as well.

Another good website for reference is

http://www.j-walk.com

I personally like the examples on cpearson.com and the layout of the site, but to each his own, I guess.

--

"If to err is human, then I must be some kind of human!" -Me