Very useful video! I have one question though: what to do if I have empty cells in the source column? When I use your formula, it lists the different values, but there is a 0 in the list because of the empty cells.
Thank you for your kind words, Amaan! I have a few videos on INDEX-MATCH with Google Sheets. Here is one: th-cam.com/video/ePkLzjC21HQ/w-d-xo.htmlsi=-BeGlqEzQykqmegh
Hi Muhammad. That's a great question! All you would need to do is add an OR statement within the IF statement. In the OR statement, you would note all conditions that you would want to include in the list. Per your question, if you wanted to get the Good Guys and Bad Guys in the same column, the code would look like this: =IFERROR(INDEX(Table1[Athlete Name],MATCH(0,IF(OR($M$2=Table1[Team],$N$2=Table1[Team]),COUNTIF($M$2:M2,Table1[Athlete Name])),0)),"") $N$2 is the word combination "Bad Guys" in this example. I have not tested the code because I don't have the file used to create the video, but I hope this helps and makes sense. Thank you!
Hi I have used =counta(unique) which gave me unique value. But I want to add another criteria to this. If another column has yes/no I want to know how to get unique value if it’s yes. How would i do this
Hi there! You might decide to include FILTER to specify your criteria. Imagine your "yes/no" is in Column C and your values are in Column B. =COUNT(UNIQUE(FILTER(B1:B10,C1:C10="yes"))) This formula should give you a count of the unique values in column B whereby the value is also "yes" in column C. I hope this helps.
You’re the man bro, thank you very much. I need to share my workbook with non-office 365 users. This solved my problem
Fantastic! Thank you for watching.
Thanks bro. You made the most accurate video for the alternate to unique function across the whole youtube.
Thank you. I appreciate your kind words!
Very useful video! I have one question though: what to do if I have empty cells in the source column? When I use your formula, it lists the different values, but there is a 0 in the list because of the empty cells.
That is a great question. I would include logic in the formula to ignore blank values (using IF criteria). I hope this helps. Thank you!
yeah i figured it out
Nice video man quite helpful it would be even if you could make a short regarding the index-match formula and explain the formula you used briefly
Thank you for your kind words, Amaan! I have a few videos on INDEX-MATCH with Google Sheets. Here is one: th-cam.com/video/ePkLzjC21HQ/w-d-xo.htmlsi=-BeGlqEzQykqmegh
Thank you very much for the video, can you share the file to practice?
Hi there! I cannot, but feel free to copy the structure and contents of what you see into your own file. I hope this helps.
Detail explanation, thanks man! This solved my work attendance sheet. What if I want to combine Good Guys and Bad Guys in 1 column?
Hi Muhammad. That's a great question!
All you would need to do is add an OR statement within the IF statement. In the OR statement, you would note all conditions that you would want to include in the list.
Per your question, if you wanted to get the Good Guys and Bad Guys in the same column, the code would look like this:
=IFERROR(INDEX(Table1[Athlete Name],MATCH(0,IF(OR($M$2=Table1[Team],$N$2=Table1[Team]),COUNTIF($M$2:M2,Table1[Athlete Name])),0)),"")
$N$2 is the word combination "Bad Guys" in this example.
I have not tested the code because I don't have the file used to create the video, but I hope this helps and makes sense. Thank you!
Excellent bro❤
Hi I have used =counta(unique) which gave me unique value. But I want to add another criteria to this. If another column has yes/no I want to know how to get unique value if it’s yes. How would i do this
Hi there! You might decide to include FILTER to specify your criteria. Imagine your "yes/no" is in Column C and your values are in Column B.
=COUNT(UNIQUE(FILTER(B1:B10,C1:C10="yes")))
This formula should give you a count of the unique values in column B whereby the value is also "yes" in column C. I hope this helps.
which version of excel are you uszing as i am getting an error while adding budget(mln)column the orginal budget is changing into #Name please help me
Thanks
Hiw do I input this in Data Validation list?
Hi Tarun. I don't fully understand your question. If you have time to elaborate on the task, I might be able to offer better support. Thank you!
Bro Formula is confusing, Help me understand
رائع
Quite a messy instruction that is. I hope this isn't your best one 🙂