A series of COUNTIF formula

[Annmarcook]

Good Morning,
I'm new to this forum and to excel. I have created a formula that contains a series of COUNTIF calculations. I tried COUNTIFS but I couldn't persuade it to handle counting cells that are not in a continuous range. This is the formula: =COUNTIF(Geography!C36:F36,"<>blw")+COUNTIF(Geography!H36:I36,"<>blw")+COUNTIF(Geography!K36:M36,"<>blw")+COUNTIF(Geography!O36:P36,"<>blw")+COUNTIF(Geography!R36,"<>blw")+COUNTIF(Geography!W36,"<>blw")+COUNTIF(Geography!Y36:Z36,"<>blw") and it works okay. However, I now need to divide the final total by another CELL. Simply adding /B11 doesn't work. Has anyone got any ideas please?

 

[jasonb75]

You need to enclose the whole thing in parentheses before dividing.

=(COUNTIF(Geography!C36:F36,"<>blw")+COUNTIF(Geography!H36:I36,"<>blw")+COUNTIF(Geography!K36:M36,"<>blw")+COUNTIF(Geography!O36:P36,"<>blw")+COUNTIF(Geography!R36,"<>blw")+COUNTIF(Geography!W36,"<>blw")+COUNTIF(Geography!Y36:Z36,"<>blw"))/B11

You have used the correct method though, countifs or other similar functions do not work with split ranges.

Another way that might work, you have 9 columns that should not be counted so in theory,

=(COUNTIF(Geography!C36:Z36,"<>blw")-9)/B11

although that is assuming that none of the excluded columns can contain "blw"