Excel Count unique values beetween 2 columns

0 votes

I need to count the number of times the first column's values deviate from those of the other column(s), for instance:

example

| 103942 | 820 |
| 103942 | 819 |
| 103953 | 819 |
| 103953 | 819 |
| 103953 | 819 |
| 103953 | 819 |
| 103953 | 820 |
| 103956 | 819 |
| 103956 | 819 |
| 103956 | 819 |
| 103956 | 819 |
| 103956 | 819 |
| 103956 | 820 |
| 103956 | 820 |
| 103956 | 819 |
| 103956 | 819 |
| 103956 | 818 |
| 103956 | 818 |
| 103956 | 819 |
| 103956 | 818 |
| 103956 | 819 |
| 103956 | 820 |
| 104157 | 818 |
| 104157 | 819 |
| 104157 | 820 |
| 104978 | 819 |

Is possible with only one formula? 

Feb 23, 2023 in Others by narikkadan
 • 63,420 points • 324 views

1 answer to this question.

0 votes

Try this :

enter image description here

• Formula used in cell D3

=LET(x,UNIQUE(A1:A26),
VSTACK({"","Uniques"},HSTACK(x,BYROW(x,LAMBDA(m,SUM((m=A1:A26)/COUNTIFS(A1:A26,A1:A26,B1:B26,B1:B26)))))))
answered Feb 23, 2023 by Kithuzzz
 • 38,010 points

Related Questions In Others

0 votes
1 answer

Count Function for identifying the Unique values in Excel

If the 9th row is empty: =COUNTA(UNIQUE(TOCOL(F8:R10)))-1 If not ...READ MORE

answered Apr 6, 2023 in Others by narikkadan
 • 63,420 points • 298 views
0 votes
1 answer

Removing duplicates from Excel rows by adding values of some columns

Copy the first three columns to another ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
 • 63,420 points • 549 views
0 votes
1 answer

Create unique rows in Excel with limited data to be used in multiple columns

This setup isn't readily generalizable, though since ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
 • 63,420 points • 520 views
0 votes
1 answer

Excel Formula - if values in columns all contain X then return

You can use the AND function: = IF(AND(A3="OK";B3="OK";C3="OK");"everything ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
 • 63,420 points • 1,164 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
 • 13,670 points • 1,414 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
 • 13,670 points • 1,480 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
 • 13,670 points • 727 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18, 2022 in Others by gaurav
 • 23,260 points • 2,468 views
0 votes
1 answer

Count number of equal values in two rows in Excel

 Use this formula: =SUM(--(A2:E2=$A$1:$E$1)) READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
 • 38,010 points • 341 views
0 votes
1 answer

Excel formula to get certain cell if the value is between 2 numbers

So, first with vlookup(): A formula so you ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
 • 38,010 points • 728 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.