Counting distinct values in relation to text values in a separate coulumn

1. Counting distinct values in relation to text values in a separate coulumn

Hi there everyone,

Hope somebody can help, I'm a bit of an Excel novice and can't get my head around this one.

I have a list of alphanumeric figures in column A, and a numeric value in column B. I need to count how many figures in column B are not equal to 0, but I only want to count them once per value in column A. Hope that makes sense, I have (hopefully) attached a short example. The value I am looking for the formula to return is 3, because 16R008A appears twice.

I have managed to create a formula just to count the unique values in A, is there anyway of modifying this to help with my query?

=SUM(IF(FREQUENCY(MATCH(WH16,WH16,0),MATCH(WH16,WH16,0))>0,1))

WH16 is the name range for my data in column A.

Would appreciate any help, thanks for your time.

Chris

2. Re: Counting distinct values in relation to text values in a separate coulumn

Welcome to the Board; one option

Formula:
`Please Login or Register  to view this content.`

3. Re: Counting distinct values in relation to text values in a separate coulumn

edit: ignore the above as would fail if first instance for given value in A was 0

Formula:
`Please Login or Register  to view this content.`

4. Re: Counting distinct values in relation to text values in a separate coulumn

=sumproduct((b2:b12<>0)*(match(a2:a12,a2:a12,)=row(a2:a12)-1)*1)

5. Re: Counting distinct values in relation to text values in a separate coulumn

Please try this and press Ctrl+Shift+Enter

=SUM(--(FREQUENCY(IF(B2:B14,MATCH(A2:A14,A2:A14,)),ROW(A1:A14))>0))

6. Re: Counting distinct values in relation to text values in a separate coulumn

AMAZING!!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1