Countifs based on multiple criteria does not work with blank cells

zimilak

New Member
Joined
Jul 14, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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
 

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
Joined
Dec 30, 2008
Messages
13,549
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,067
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
13,549
Office Version
  1. 365
Platform
  1. Windows
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).
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top