Thanks:  0
Likes:  0

1. Can anyone help me come up with a function that will:

Scan a list of names and count how many of the names have a corresponding "yes" value beside them. However, if the name is listed twice, the function should ignore the redundant listings. Therefore only counting a recurring name the first time.

The data would be in two columns, say A1:B10.
With names in Coulmn A and "Yes" or "No" in column B.

2. Hi SB
Same message I sent to the last guy.
Someone will probably give you a nice formula. If not this would be easy to accomplish with VBA.
I'll post a small macro if no one helps you out.
Tom

3. Approaches include the following:

1. Use a Pivot Table and hide the Grand total.

2. Use =COUNTIF(\$A\$2:A2,A2) copy down
recap the count of column
and "Yes" criteria with Sumproduct

3. Concatenate 2 columns and evaluate the number that are unique

- =A2&|&B2
number unique with Array Formula
=SUM(1/COUNTIF(rA,rA))

rA is name of range of concatenated info
Enter the Array formula with Ctrl-Shift-Enter

[ This Message was edited by: Dave Patton on 2002-03-26 13:41 ]

4. Thanks for the formula but I am having trouble understanding it. I can't get it to work.

5. Which approach did You decide to try?

There are pros and cons to each solution.

6. You can use Data Filter Advanced to
give you a list thaat meet your criteria.

Use Counta to count the names in the list.

It is Your choice what approach you want to use.

7. On 2002-03-26 13:02, Dave Patton wrote:
Approaches include the following:

1. Use a Pivot Table and hide the Grand total.

2. Use =COUNTIF(\$A\$2:A2,A2) copy down
recap the count of column
and "Yes" criteria with Sumproduct

3. Concatenate 2 columns and evaluate the number that are unique

- =A2&|&B2
number unique with Array Formula
=SUM(1/COUNTIF(rA,rA))

rA is name of range of concatenated info
Enter the Array formula with Ctrl-Shift-Enter

[ This Message was edited by: Dave Patton on 2002-03-26 13:41 ]
Sumproduct won't work with names. I suggest the pivot table approach.

-rh

8. Russell

You stated
" Sumproduct won't work with names. "

Would you clarify?

9. My 2 cents:

All of the suggestions by Dave Patton, including Adv Filter combined with COUNTA would work.

Another approach that would not require an additional column that concatenates the data from 2 columns is what follows.

lets consider the sample in A4:B9.

{"Name","Y/N";
"ali","yes";
"jon","no";
"jon","yes";
"dan","yes";
"ali","yes"}

Try:

=SUM(IF(FREQUENCY(MATCH(A5:A9&B5:B9,A5:A9&B5:B9,0),MATCH(A5:A9&B5:B9,A5:A9&B5:B9,0))=1,1))

normally entered.

That is quite the formula.

I was close but I could not get all the pieces to connect.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•