Using the COUNTIF function

Question

I am trying to count the number of hospital rooms on a certain floor using the COUNTIF function.  The problem is that some of the rooms have a dash (ie 451 and 451-2) so I used "4*" and all it counted was the rooms with the dash.  How can I get the formula to include the rooms without the dash

Answer 1

Hi,

This will also count room number 503.  I beliee your formula will have to be modified to:

=COUNTIF(A:A,"=4*")+COUNTIF(A:A,">=400")-COUNTIF(A:A,">500")

Answer 2

If your room numbers are in column A, starting in cell A2, then a formula like the one below will count only the rooms that start with "4" that do not have a "-" in them.

=SUMPRODUCT(--(LEFT(A2:A34,1)="4"),--(NOT(ISNUMBER(SEARCH("-",A2:A34,1)))))

If you are trying to count ALL rooms that start with 4, then use this:

=SUMPRODUCT(--(LEFT(A2:A34,1)="4"))

HTH,

Eric

Answer 3

You're correct - it would have counted any room number above 400.  I had it in my mind at the time that only one floor was dealt with on the worksheet.  But it is possible that the entire hospital could be contained on it.

Answer 4

Part of this is no doubt because entries like 451 are true numbers, while 451-2 and others with a dash are treated as text.  The "4*" search parameter searches for text.

What you could do is try to add one COUNTIF() for text matches to another for Numeric matches:

=COUNTIF(A:A,"=4*")+COUNTIF(A:A,">400")

Or you might need ">399" if there's a room 400 that needs to be counted.