Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel formulas meeting 2 criteria, one based on a concatenated column

Posted on 2014-09-24
5
Medium Priority
?
134 Views
Last Modified: 2014-09-26
I have attached a spreadsheet with a sample range of data. Column C contains concatenated numbers from column A & B. Column D is a sign-off column. I need 2 (non-array) formulas that calculates the following:

1. # of unique 6-digit numbers in Column C where Column D is not blank, and does not contain "N/A"
2. # of unique 6-digit numbers in Column C where Column D in not blank, and contains "QRY"

There is typically about 1000 rows of data. The reason for the non-array request is that this spreadsheet will be temporarily shared (I know, not good, but beyond my control and fortunately it will be short-term!) so array formulas won't work for this situation.

Thanks!
Andrea
Sample.xlsx
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
  • 3
  • 2
5 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40341443
Please find attached.
Sample.xlsx
0
 

Author Comment

by:Andreamary
ID: 40342184
Thanks, Phillip! Before I implement, I was wondering...is there a way to avoid using 'helper' columns in the solution?
0
 

Author Closing Comment

by:Andreamary
ID: 40346041
Thanks very much!

Cheers,
Andrea
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40346046
If it were a less complicated question, then you could use COUNTIFS or an array formula, but given that you have to use FIND, I don't believe so - neither COUNTIFS or array formulas work with FIND well.
0
 

Author Comment

by:Andreamary
ID: 40347166
Okay, good to know. Thank you...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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 article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 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