COUNTIF function

[mark-ainsworth]

Hi,

I am trying to be more efficient with my countif functions. I have 5 regions on a sheet that i want a formula to count how many times 'UNVAILABLE' and 'VACANT' are in the 5 regions, then subtract that number from 39. So the formula i have at the moment is

Please Login or Register  to view this content.

Can anyone help top condense the forumla?

Thanks,
Mark

[daddylonglegs]

This would condense it but beware that INDIRECT can make it more inefficient (as it's a volatile function)

=39-SUM(COUNTIF(INDIRECT({"B8:B14","B16:B25","I8:I14","I16:I24","P8:P13"}),{"Vacant";"Unavailable"}))

[mark-ainsworth]

Thanks for this.

I have used it and it is working to a degree - it seems to break on the 'THUR' tab. See attached table - the cell that counts is T25.

Can anyone offer a reason and (more importantly) a solution?

Thanks,
Mark

[daddylonglegs]

You have a "trailing space" after "UNAVAILABLE" in B8. If you edit that out then you should get a result of 36.....or change the formula so that it looks for "vacant" and "unavailable" in amongst any other text, ie. with wildcards

=39-SUM(COUNTIF(INDIRECT({"B8:B14","B16:B25","I8:I14","I16:I24","P8:P13"}),"*"&{"Vacant";"Unavailable"}&"*"))

Note that this might not be appropriate for you, it would count "Not Vacant" for instance........