# Count unique values only, over two columns in the same row

1. ## Count unique values only, over two columns in the same row

Good day all,

I have a spreadsheet where I need a formula to count unique values only, where the date (Column B) a ID number (Column D) are different. Thanks in advance for any help, have a great day!

Marco

1.jpg

2. ## Re: Count unique values only, over two columns in the same row

Not real clear.

Do you want to count the unique ID numbers for a specific date?

3. ## Re: Count unique values only, over two columns in the same row

Work Fine only for Unique ID paste this in Sheet D2

Use Ctrl+Shift+Enter after pasting the formula
=IFERROR(INDEX(Sheet1!D1:D14,MATCH(0,COUNTIF(Sheet2!\$D\$1:D1,Sheet1!D1:D14),0)),"")

4. ## Re: Count unique values only, over two columns in the same row

Sorry for the poor explanation,

I examine a product numerous time throughout the day & may make up to 10 notes (rows) on its progression, i need this to calculate as 1 inspection for that entire day not 10 on that particular product ID, however on day 2 I may perform the same amount on inspections, I now need this to calculate 2 inspections not 20, basically if the product ID is repeated in the same day to only count it once, hope this makes sense? Thanks again for your time

5. ## Re: Count unique values only, over two columns in the same row

Try this array formula**:

=SUM(IF(FREQUENCY(MATCH(B2:B20&D2:D20,B2:B20&D2:D20,0),ROW(B2:B20)-ROW(B2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

6. ## Re: Count unique values only, over two columns in the same row

Hi Tony,

Works like a charm, can't thank you enough!

7. ## Re: Count unique values only, over two columns in the same row

You're welcome. Thanks for the feedback!

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