Hi,
I'm hoping someone can help me out with this - I've been trying to figure it out for hours with no luck!
I have a spreadsheet (actually several) which have unit types (1 bedrooms, 2, bedrooms, etc) column A and colours in column B indicating their status (black=vacant, blue=occupied, etc). I have used this spreadsheet for years with a user defined formula called CountCol to basically count the colours to summarize the status. So a formula like COUNTCOL(B:B,1) will count all the black cells and return a 2 if there are 2 black cells. This basic type of counting works great and never had a problem with it.
What I'm trying to do now is count all the units of a particular type (eg 1 bedrooms) which are vacant. So I try =COUNTIFS(A:A,"1 br",B:B,CountCol(B:B,1)). However, this returns a 0 when it should be returning a 1. I think COUNTIFS() should work because if I put a value in the black cells (say 1) and use the formula =COUNTIFS(A:A,"1 br",B:B,1) it returns 1, which is correct.
I've attached the spreadsheet to this message and I'm really hoping someone can help me out with this. I've spent hours trying to google this and I just have on idea what I'm doing wrong. The logic seems to make sense so maybe it's something about the CountCol custom formula that isn't allowing it to work.
Thanks!
Bookmarks