Countifs formula meeting 2 criteria and using named ranges

Posted on 2014-03-27
Last Modified: 2014-03-27
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?

Question by:Andreamary
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 39

Assisted Solution

nutsch earned 200 total points
ID: 39960354
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?

LVL 19

Expert Comment

ID: 39960409
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,
LVL 39

Expert Comment

ID: 39960420

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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 50

Accepted Solution

barry houdini earned 300 total points
ID: 39960431
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
LVL 39

Expert Comment

ID: 39960456
Congrats on your 5th million BTW, barry!

LVL 50

Expert Comment

by:barry houdini
ID: 39960485
Hey, Thomas, thanks.......I didn't notice :)

regards, barry

Author Closing Comment

ID: 39960647
Thanks to you both!

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Gain an elementary understanding of Blockchain technology.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question