# Only including cells containing certain words / numbers in formula?

Copper Contributor

# Only including cells containing certain words / numbers in formula?

I have a new job as a sepsis specialist nurse.  At the end of the month, using all the data inputted throughout the month, I have to work out how many high risk patients received IV antibiotics within 1 hours.  I would greatly appreciate your help, as I have never worked with Excel before

So the calculation I'm after would be as follows:

Only include only patients categorized as HIGH (column I) which contains either High or Low.  Of these I need to take any patient with 01:00 (1 hour) or less (Column Q).  Then divide by the total amount of High risk patients to give me a percentage of patients that received IV antibiotics within 1 hour.  Hope that makes sense

6 Replies

# Re: Only including cells containing certain words / numbers in formula?

Create this formula:

=COUNTIFS(I2:I1000, "HIGH", Q2:Q1000, "<="&TIME(1, 0, 0))/COUNTIFS(I2:I1000, "HIGH")

Format the cell with the formula as a percentage.

# Re: Only including cells containing certain words / numbers in formula?

This is great, thank you, works perfectly!

# Re: Only including cells containing certain words / numbers in formula?

If I needed to work out the percentage under 90 minutes, what would I change in the formula?

# Re: Only including cells containing certain words / numbers in formula?

90 minutes is 1 hour and 30 minutes, so change TIME(1, 0, 0) to TIME(1, 30, 0)

# Re: Only including cells containing certain words / numbers in formula?

I have attached a sheet and formula is applied in cell G-4. I hope it will help.

# Re: Only including cells containing certain words / numbers in formula?

Thank you. This is very much appreciated.