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.

AttachmentSize
testing.xlsx29.49 KB
Nick's picture

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,))

Nick's picture

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

Almir's picture

If you query data from a database...

Use DISTINCT in your SQL to get unique results.