Solved

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

Posted on 2014-09-24
5
131 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
  • 3
  • 2
5 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

809 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