[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

Countifs formula meeting 2 criteria and using named ranges

Sheet 1 contains data and has 2 named ranges: AIAStatusRange and TypeChange_Range. On Sheet 2 I am trying to use the 'Countifs' formula that references the data on Sheet 1 to find all instances that meet the following two criteria:

=COUNTIFS(AIAStatusRange,"=Not started",TypeChange_Range,"=New")

Unfortunately, I am getting the #VALUE error. Any suggestions?

Thanks,
Andrea
0
Andreamary
Asked:
Andreamary
2 Solutions
 
nutschCommented:
Try removing the equal sign, though I don't think that's the issue.

=COUNTIFS(AIAStatusRange,"Not started",TypeChange_Range,"New")

Are both your ranges one column wide and of equal number of rows?

Thomas
0
 
helpfinderIT ConsultantCommented:
if the equal sign is not a problem as nutsch wrote then it´s hard to tell you what´s going on. It could be a typo in your formula but because we do not see the excel workbook you are working with we do not know if your range name is really the same as you have it in the formula or not,
0
 
nutschCommented:
@helpfinder:

if the name was typed wrong, the error would be #NAME!, with a #VALUE! error, it is most likely two ranges of different sizes, or more than one column per range.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
barry houdiniCommented:
I agree, Thomas - the ranges can be multiple columns....but both need to be the exact same size and shape, i.e. same number of columns and same number of rows, otherwise you get #VALUE! error. That applies to  SUMIFS and AVERAGEIFS too.

regards, barry
0
 
nutschCommented:
Congrats on your 5th million BTW, barry!

Thomas
0
 
barry houdiniCommented:
Hey, Thomas, thanks.......I didn't notice :)

regards, barry
0
 
AndreamaryAuthor Commented:
Thanks to you both!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now