Countifs based on multiple criteria does not work with blank cells

[zimilak]

I read online that countifs does not work unless there are the same amount of data in each column being addressed. My formula shows:
=countifs(Name,"John Doe",Age,">20",Sport,"Basketball")
I am using named ranges, "Name, Age, & Sport". My Sport column of data may have empty cells. My formula works with just name & age, but it gives me #value when I add the last criteria. If anyone can help, I'm driving myself nuts trying to figure this out. Basically I'm searching for a count of all John Does who are older than 20 and likes basketball, ignore the blank cells. TIA

 

[jasonb75]

Empty cells shouldn't be a problem, the error will occur if the ranges are of unequal size.

In your example formula, Name, Age, and Sport must all contain the same number of rows and columns.

 

[Joe4]

Welcome to the Board!

It does not matter if there are blank cells in the range or not.
What matters is that your named range are all the same size.

For example, if the named range "Name" was range A1:A20,
the named range "Age" was B1:B20,
and the named range "Sport" was C1:C10,
it would not work, as the first two ranges contain 20 cells each, and the third only contains 10.
They must all have the same number of cells in each named range.

Whether or not any of the cells in the named ranges are blank or not does not matter (at least, it will not cause the formula to error out for this reason).

 

[jasonb75]

Just adding a bit of clarity that I didn't think to include in my earlier reply. If Name, Age, and Sport are dynamic named ranges then the blanks could be causing one of more of them to size incorrectly depending on the formula used to set the range and the position of the blanks in the data. When setting up named ranges like this it is preferable to use one of the columns to set the range sizes rather than setting each individually.

For example..

Name:- LastRow
Refers to:- =MATCH("zzz",$A:$A)

Name:- Name
Refers to:- =$A$2:INDEX($A:$A,LastRow))

Name:- Age
Refers to:- =$B$2:INDEX($B:$B,LastRow))

Name:- Sport
Refers to:- =$C$2:INDEX($C:$C,LastRow))

Which would ensure that all of the ranges have the same number of rows as column A (assuming that is the Name column).