# Countifs based on multiple criteria does not work with blank cells

#### zimilak

##### New Member
I read online that countifs does not work unless there are the same amount of data in each column being addressed. My formula shows:
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

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### jasonb75

##### Well-known Member
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

##### Well-known Member
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).

Replies
4
Views
91
Replies
5
Views
92
Replies
7
Views
204
Replies
1
Views
155
Replies
4
Views
428

1,141,606
Messages
5,707,360
Members
421,503
Latest member
Rickys03

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back