Improve company productivity with a Business Account.Sign Up

x
?
Solved

Countifs formula meeting 2 criteria and using named ranges

Posted on 2014-03-27
7
Medium Priority
?
361 Views
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?

Thanks,
Andrea
0
Comment
Question by:Andreamary
7 Comments
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 800 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?

Thomas
0
 
LVL 19

Expert Comment

by:helpfinder
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,
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39960420
@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.

 
LVL 50

Accepted Solution

by:
barry houdini earned 1200 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
0
 
LVL 39

Expert Comment

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

Thomas
0
 
LVL 50

Expert Comment

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

regards, barry
0
 

Author Closing Comment

by:Andreamary
ID: 39960647
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
Excel allows various different methods to link Excel files to each other. This includes relative paths, mapped drives (or the local drive) and UNC paths. UNC paths are the least robust of the three.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

606 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