CountIF

  • Thread starter Thread starter Brendon
  • Start date Start date
B

Brendon

I want to do a count of patients that are divided up into two groups.

Patients in the morning (before 11:59) that waited more than 20 mins
Patients in the afternoon (after 11:59) that waited more than 20 mins

Here's what I have, but it doesn't seem to calculate right.
=IF(E:E,"<0.5",COUNTIF(H:H,">" & TIME(0,20,0)))
which gives me <0.5

I'm missing something, but I can't figure it out.

Brendon
 
You're creating a "hole" at 12 pm:
before 11:59
after 11:59
Click to expand...

Here's what you asked for:

=SUMPRODUCT(--(E1:E100<0.5),--(H1:H100>TIME(0,20,0)))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.
 
You have not clearly told us what data you have in columns E and H.
If column E has real times (like 8:00) and H has a number (like
12)representing minutes waited:
=SUMPRODUCT(--(E1:E100<TIME(12,0,0)), --(H1:H100>20))
Do not use full columns
=SUMPRODUCT(--(E:E<TIME(12,0,0)), --(H:H>20))
unless you have Excel2007
best wishes
 
OK, this worked, thank you.

Two other questions.

I changed the E:E to E2:E2000 and H:H to H2:H2000
I'm doing this via VB, is there a way to declare a variable (or two)
at the beginning and then just change the variable and have it change
the variable in the code so that if I wanted to go from 2:2000 to say
2:5000, I just update the variable.

Also, you said there was a whole, will the below code, fix that hole?

Brendon
 
You'd have to post the code. I'd recommend you post it in the programming
forum. That's where all the programmers hangout!
You're creating a "hole" at 12 pm:
before 11:59
after 11:59
Click to expand...

Actually, the "hole" is at 11:59 but that's easily fixed. Just change the
test. Maybe use <=0.5 which makes the boundary 12 PM. And then, for the
"other end" use >0.5.

--
Biff
Microsoft Excel MVP


OK, this worked, thank you.

Two other questions.

I changed the E:E to E2:E2000 and H:H to H2:H2000
I'm doing this via VB, is there a way to declare a variable (or two)
at the beginning and then just change the variable and have it change
the variable in the code so that if I wanted to go from 2:2000 to say
2:5000, I just update the variable.

Also, you said there was a whole, will the below code, fix that hole?

Brendon
 
You showed a test for >11:59 and another for <11:59
Biff is talking about the 'hole' at exactly 12:00 - you are not capturing
any records with exactly that time
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

OK, this worked, thank you.

Two other questions.

I changed the E:E to E2:E2000 and H:H to H2:H2000
I'm doing this via VB, is there a way to declare a variable (or two)
at the beginning and then just change the variable and have it change
the variable in the code so that if I wanted to go from 2:2000 to say
2:5000, I just update the variable.

Also, you said there was a whole, will the below code, fix that hole?

Brendon
 
Hi

You can also consider that since 20/60*24 = .01389... you can substitute
this approximation for the TIME(0,20,0) function. The plus side is one less
calculation and a shorter formula, the downside is the approximate nature and
lack of clarity.

=SUMPRODUCT(--(E1:E100<0.5),--(H1:H100>0.0139))

One of your two formulas should read <=0.5 or >=.5 as has been implied in
previous posts.

You can also replace TIME(0,20,0) with

TIME(,20,)
 
OK, so I'll be more specific.

Column E has Patient Arrival Times (I'm using E2:E2000)
Column H has the amount of time from when they arrived at the office,
to when the technician started assisting them. (H2:H2000)

The formulas give me results greater than 1000, when there's only 640
patients. So I don't think it's calculating correctly.

Brendon
 
Try this:

=SUMPRODUCT(--(E2:E2000<>""),--(E2:E2000<0.5),--(ISNUMBER(H2:H2000)),--(H2:H2000>TIME(0,20,0)))


--
Biff
Microsoft Excel MVP


OK, so I'll be more specific.

Column E has Patient Arrival Times (I'm using E2:E2000)
Column H has the amount of time from when they arrived at the office,
to when the technician started assisting them. (H2:H2000)

The formulas give me results greater than 1000, when there's only 640
patients. So I don't think it's calculating correctly.

Brendon
 
Biff - AWESOME!!!

Thank You.


Try this:

=SUMPRODUCT(--(E2:E2000<>""),--(E2:E2000<0.5),--(ISNUMBER(H2:H2000)),--(H2:H2000>TIME(0,20,0)))

--
Biff
Microsoft Excel MVP


OK, so I'll be more specific.

Column E has Patient Arrival Times (I'm using E2:E2000)
Column H has the amount of time from when they arrived at the office,
to when the technician started assisting them. (H2:H2000)

The formulas give me results greater than 1000, when there's only 640
patients.  So I don't think it's calculating correctly.

Brendon
Click to expand...
 
Back
Top