Count Function for identifying the Unique values in Excel

0 votes

Here I need to identify the overall count by Ignoring the duplicate values in both rows.

Example:

enter image description here

I need to compare whether the AAAA value is displayed in the top row or the same row here (other cells). In this instance, ignoring the duplicates count results in an overall total of 7.

I tried this function :

=Countif(F10,"<>"&L8) +Countif(H10,"<>"&L8).., --> 

While I need to compare both rows, this statement only compares for a single condition. referring to Row 8 and Value Row 10 for each cell.

Apr 6, 2023 in Others by Kithuzzz
• 38,000 points
666 views

1 answer to this question.

0 votes

If the 9th row is empty:

=COUNTA(UNIQUE(TOCOL(F8:R10)))-1

If not:

=COUNTA(UNIQUE(TOCOL(VSTACK(F8:R8,F10:R10))))-1

Result:

enter image description here

answered Apr 6, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

How to use the INDIRECT function in Excel

Try this: =INDEX(INDIRECT($A$1&"!$1:$1048576"),MATCH($B$1,INDI ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
• 63,600 points
918 views
0 votes
1 answer

How do you calculate the Quintile for groups of rows in Excel?

Use this formula: =MAX(1,ROUNDUP(10*PERCENTRANK($C:$C,$C2,4),0)) To divide into whichever many ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 63,600 points
1,374 views
0 votes
1 answer

Counting distinct values in excel - frequency function

You can use COUNTIF to count the ...READ MORE

answered Oct 20, 2022 in Others by narikkadan
• 63,600 points
494 views
0 votes
1 answer

How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,600 points
1,158 views
0 votes
1 answer

Excel automatically converting 7 digit CAS number to another number (date?)

Looks like you could use: The formula in D2: =SUBSTITUTE(F ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 63,600 points
1,050 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,690 points
4,133 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,690 points
2,013 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,690 points
994 views
0 votes
1 answer

Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

answered Oct 10, 2022 in Others by narikkadan
• 63,600 points
1,221 views
0 votes
1 answer

Extract unique value from the range in Excel

The portability of spreadsheet functions like UNIQUE() ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,600 points
1,293 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