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

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
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.
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

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

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

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

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

791 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