Nesting formulas from a Query
I have a query that loads real time data into an excel sheet. In Column A I have 2 different names but each name is repeated more than once depending on how many responses come back from each name.
In column Q I have three options that I need to quantify depending on the name and how many responses come back from that name.
For example, column A has 473 responses for BHS, 257 responses for CHS and so forth. For each response there options are generated in Column Q; greater than 1(random numbers), 0, or null.
What I need to do is lookup the name "BHS" and count how many responses in column Q there are for greater than 1, 0 & null into three separate responses.
Per each number greater than one I would count it as 1, for all the numbers= 0 I would count each one as 1. And for all the nulls I would count each one as 1. Into three separate categories. See sheet attached.
| Attachment | Size |
|---|---|
| testing.xlsx | 29.49 KB |
take a look at
take a look at this:
http://excelexperts.com/formula-multiple-entries
if that doesn't solve your problem, add an example file
(log in and edit your post)
Tried it/ Uploaded a sample
Hey Nick
First off I would like to thank you for responding. I edited my post and attached a sample of what I am trying to do. If I can help clarify please let me know.
This is what I have tried
=COUNTIF(Results!A:A,VLOOKUP("CHS", Results!A:Q,17,))
I'll start u off then.. to
I'll start u off then..
to calculate the number of entries in col A that have a value > 1 in col Q... you'd use this formula:
=SUMPRODUCT((A1:A730="BHS")*(Q1:Q730>1))
Works Perfect
Thank You very much Nick
If you query data from a database...
Use DISTINCT in your SQL to get unique results.