Solved

Countifs formula meeting 2 criteria and using named ranges

Posted on 2014-03-27
7
317 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
[X]
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
7 Comments
 
LVL 39

Assisted Solution

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

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
Technology Partners: 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

by:
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
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

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!

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.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

740 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