0

Hello is there any function to count the distinct values of 3 or 4 columns so i do not have temp tables and other things. Also excel vba is not a case.

Like i want to count distinct languages in the table (it did not let me to place it as data). Do you want more info for help? Also some cells could be empty.

enter image description here

Improve this question
5
  • Yes there is. What specifically, depends on your data. If you would mock up some test data and expected output, we can help more. Otherwise the answer to your question is, "Yes". Commented Jan 8, 2020 at 14:46
  • COUNTIF() will do this, but I might use data validation to select which language is to be counted, needs a list of unique languages though. Commented Jan 8, 2020 at 14:53
  • it should be function t be written in cell. no work with menus so i can just place it in a cell and count Commented Jan 8, 2020 at 14:55
  • I have deleted my answer, as what the OP is showing currently is not their actual data setup. There may be more than one language per cell separated by a , Commented Jan 8, 2020 at 15:09
  • no sorry i will make a different question place your answer please the only part that i want to be included here is what if a cell in the table is empty, but always only one value in cell exists no commas nothilg like this Commented Jan 8, 2020 at 15:13

2 Answers 2

Reset to default
2

A simple reciprocal of a COUNTIF in a SUMPRODUCT:

=SUMPRODUCT((A2:C10<>"")/(COUNTIF(A2:C10,A2:C10)+(A2:C10="")))

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

perfect. may i make it more difficult. what if the data where already concatenated with , in one column so you had one column and lets say the first row of data was : Armenian,English,French
The languages in one row could be 2 and in another could be 5
you will need to separate them. That is a different question than the one posed here.
yes i know this is a different question, do you think i should make also this one?
also the function has problem if a cell is empty
|
0

Taking help of Scott i fount that 

=SUMPRODUCT((A6:C14<>"")/(COUNTIF(A6:C14;A6:C14&"")))

will do the work

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.