Calculating unassigned locations

[dmcnab]

Hi all....I need some help setting up a formula that will count unassigned locations. The attached workbook shows the number of unassigned rooms in column D (these rooms are shown in columns DI:EG).....these numbers are incorrect and the "should be" numbers are shown in column E. The formula that I am using in column D was originally set up on the assumption that room numbers (eg: 231, 232, 234 etc) would only be numbers (without letters). As it turns out, some numbers are combinations of numbers+letters.......I need help with the formula in column D so that it produces the 'correct' numbers in column D...thanks

[HansV]

In D15:

=COUNTA($DJ$14:$EH$14)-COUNTIF($DJ15:$EH15,"")-COUNTIF($DJ15:$EH15,"*(am)")/2-COUNTIF($DJ15:$EH15,"*(pm)")/2

[dmcnab]

Hi Hans...I tried the formula you suggested (altho I changed it from DJ to DI, b/c DI is the starting column -- I assumed that your use of DJ was a typo).....but it still only works in some cases -- I attach a sample of the same workbook using your formula (altho changed from DJ to DI) and you will see it is correct only in row 21 (I'm not sure why?...perhaps b/c of the '--' in column DO) and always short by 1 in other rows...

[HansV]

I had inserted a column to test the formulas, hence the shift. I forgot to shift back.
But if you change DJ to DI, you should also change EH to EG:

=COUNTA($DI$14:$EG$14)-COUNTIF($DI15:$EG15,"")-COUNTIF($DI15:$EG15,"*(am)")/2-COUNTIF($DI15:$EG15,"*(pm)")/2

[HansV]

Oh, and how should -- be interpreted? Are there other intricacies you haven't introduced yet? Please do so now, instead of unveiling them one by one.

[HansV]

And can you explain why D20 should be 6?

[dmcnab]

Yes...D20 should be 5.5 (not 6)......for the most part, these locations aren't used on Sat or Sun, and so when not in use on a Sat or Sun, they show as blank.....I could do the same, for example, in col DO, but I chose to show non-use on weekdays using the '--' (instead of leaving it blank).....there isn't any significance to it, but I figured that if all 'non-use' days (weekend or weekday) were shown as '--', then the page would look cluttered and I was trying to avoid that.......as far as other intricacies, the only one that I am using (but removed from my formula) is an IF condition at the beginning of the formula that says that IF (a day is a stat holiday)="Closed", and I will add that back into the formula once I get it counting properly, so I don't think you have to worry about that qualifier for the moment

[HansV]

How about

=COUNTA($DI$14:$EG$14)-COUNTIF($DI15:$EG15,"")-COUNTIF($DI15:$EG15,"*(am)")/2-COUNTIF($DI15:$EG15,"*(pm)")/2-COUNTIF($DI15:$EG15,"--")