No need to change the criteria range to absolute value. Just use D3# to fill down as the array result. =Countif(B3:B32,D3#), in that way, you can avoid annoying changes to absolute and copy down.
Man I could have used this a few months ago… I had to go through and count over 1500 responses to a survey by hand… I then had to separate which word they selected into a list separated by commas, then had to take the list apart and make new rows for all of the instances with multiple responses. It took me foreeeeevvvvvveeeeer.
Great and practical Excel instruction! Next level would be, in my opinion, to turn the source range into a table and use Power Query's Group By feature with the Count Rows aggregation and thus eliminate formulas altogether.
Good except you should use # at the end so the formula spills in proportion with the unique spill function so =countif($B$3:$B$32,D3#) Why do this ? I explained to a person, dragon, the advantage to using hash reference rather than copying the formula down as one does with non-spill functions. Answer ls here: ruclips.net/video/D9to9vnIuOg/видео.html
@@ParadiseDB7 Unnecessary ?? it allows your formula to be dynamically increasing and decreasing insync with your unique spill function automatically copying the formula down to the last unique value. If my unique values increase with new unique values or decreases with the elimination of unique values you will otherwise have to keep adjusting the count formula to align properly. What advantage do you see in NOT having the formula be dynamic with a simple hash tag at the end ? 🤔🤔
How to separate /delete landlines 📞 phone numbers which is mixed with cellphone numbers? We do skip tracing people’s contacts numbers, but it mixed with both cell n landline numbers. We only need the cell numbers. Thx 🙏
I have used a unique function at row level and there is no outcome however I used transpose to convert row data to column and then applied unique with transpose, voila, unique function has been worked. question: does a unique function work on row level data or not?
Would this work for a wedding invite list? I need to send invites but some households have more than 1 person. I wanted a count for who has RVSP'd, who's pending and who said no.
So. Here’s a wild request, if I wanted to list how many times the number 1 appears in a series of numbers ranging from 0-364 with most numbers spaced out every 5 but there are a fair amount of deviations like 5, 10, 15, 20, 25, 28, 29, 30, 31, 35 all the way to 364 Can Excel tell me how many times 1 appears? Or how many times 3 appears? Rather How many times each digit 0-9 appears?
Can you please help me? Mine's just showing #NAME? when i try the =UNIQUE() formula. Pls help.. and is this possible also if i need to count the number of time it appears from a different sheet? Thank you
If you don't use F4, when you drag down the COUNTIF function, it will begin the range on the row the criteria is on. I.E: Coke is on D4, and without F4, your COUNTIF will also start of B4... but you want it to always start counting on B3.
But the Drink column will probably change over time. Unique will automatically update, but the counts column will not add a row automatically. Got a trick for doing that besides an if empty return empty else count? Also the countif works on small spreadsheets, but get a few hundred thousand rows and it gets very slow.
In the first argument of your Countif, use OFFSET(first cell of your list, 0, 0, COUNTNA(whole column of your list),1). In terms of speed, maybe you should try a Pivot table. Selects the whole column, then remove blanks in the Pivot Table Options.
Select one of Column B in the cart then Ctrl+T. Table function is activated. If you input new data B33, automatically reflects unique formula result without modifying the formula.
You definitely can! Each have their use cases. Functions can be helpful when you need the data for an argument in another function when creating custom calculations.
Unique formula is new to me, thank you. I've been copying list elsewhere, going to data then remove duplicates. Great tip 👍
UNIQUE I learnt last week but if the list is a number of locations or "square of data" then use VSTACK in combination 😀
me too 😂
Select Distinct
No need to change the criteria range to absolute value.
Just use D3# to fill down as the array result.
=Countif(B3:B32,D3#), in that way, you can avoid annoying changes to absolute and copy down.
What does the hashtag do in the D3# part?
will it help in vlookup formula?
@@imransharif8387 if using office 365 yes.
@@KhaledTheSaudiHawkIIthe hashtag references the entire dynamic array.... Really cool new feature.
@@favreje thanks for teaching me something new ♥️
Man, where were you ? Every short is right the thing I need to use this week. Thank you!
This has just saved a shit load of work for a large register I'm using. Absolutely spot on.
Pivot tables?
Man I could have used this a few months ago… I had to go through and count over 1500 responses to a survey by hand… I then had to separate which word they selected into a list separated by commas, then had to take the list apart and make new rows for all of the instances with multiple responses. It took me foreeeeevvvvvveeeeer.
The "F4" bonus! 🤯🤯🤯
F4 to make an absolute reference. What a prize!
Wall Street could use your skills. Fantastic!
You have solved a question I have had for months. Thank you
Great and practical Excel instruction! Next level would be, in my opinion, to turn the source range into a table and use Power Query's Group By feature with the Count Rows aggregation and thus eliminate
formulas altogether.
I would love to see tutorials like that also for LibreOffice Calc.
This is cool. I've been using pivot or remove duplicate the wholetime
I love your videos!
In new excel you can map a lambda against the unique array all inside a single cell, spill into the two columns with hstack ^^
A million thanks!!!
Awesome, video! Tks, bro!
Super! Did not know about this one!!!
Seems like a quick pivot would be faster with a little list like this.
Hot damn I've been copying over my collumn and removing duplicates that's awesome
Love you bro, helped me a lot
Totally what I needed today. 👍 Thanks
I ha to do this last moth and I was going crazy! Thank you so much for this videos you post
Everytime I see your video, my mind is blown. This is amazing stuff¡
Thankyou so such ❤️❤️❤️
I need to practice this is so important
Excellent 😊
Dude. Thank you.
this good for spare part
Damn I need to reup my Excell subscription. I can use all of these tips.
This is a good one
I'm starting to understand why excel competitions exist
I would use Pivot for this purpose
Pivot tables! Pivot tables! Yes, indeed!
I am so not going to remember this when I need it. I'll just have to go make a pivot table😊
Perfect
Oh dang, I did that the slow way like 10 times just today 😂
Is it too early to say, “I love you?” 😂 Seriously though, thank you for all the great tips! So happy my algorithm knew I needed this! 🖤🦇
You’re a life saver❤
No need to highlight, just press F4.
super Bro :)
Thanks
Those $ to keep formulas from moving, perfect, thank you lol
Thanks Sir
I literally could have used unique function yesterday
wait, what? highlight then press F4?
.... the hero I didn't know I needed.
It's asking # name
or create a pivot...
❤
Table + pivot table = done
What’s the alternative for F4 in Mac?
Wow
Good except you should use # at the end so the formula spills in proportion with the unique spill function so =countif($B$3:$B$32,D3#)
Why do this ?
I explained to a person, dragon, the advantage to using hash reference rather than copying the formula down as one does with non-spill functions.
Answer ls here:
ruclips.net/video/D9to9vnIuOg/видео.html
It's not needed
@@ParadiseDB7 its a better practice.
@@acWeishan it's unnecessary
@@ParadiseDB7 Unnecessary ??
it allows your formula to be dynamically increasing and decreasing insync with your unique spill function automatically copying the formula down to the last unique value.
If my unique values increase with new unique values or decreases with the elimination of unique values you will otherwise have to keep adjusting the count formula to align properly.
What advantage do you see in NOT having the formula be dynamic with a simple hash tag at the end ? 🤔🤔
@@acWeishan because it's not needed, and it doesn't even do what you say
👍
Thanks, you helped me last minute saving my life. Thank you very much ❤ ⁴⁴²⁴
How to do the other way round, which is to convert the right table to the left one?
How to separate /delete landlines 📞 phone numbers which is mixed with cellphone numbers? We do skip tracing people’s contacts numbers, but it mixed with both cell n landline numbers. We only need the cell numbers. Thx 🙏
Can you do the same thing with QUERY()?
I have used a unique function at row level and there is no outcome however I used transpose to convert row data to column and then applied unique with transpose, voila, unique function has been worked.
question: does a unique function work on row level data or not?
Looks very useful, but I missed why the third column populated at the end
It duplicated the countif formula in the top box to all other boxes below it
@@redhotz21 Thank you 😊 I don’t see what he’s doing to duplicate it. Did he just hit enter, drag it down to the bottom, or something else?
Excel infers that you want the column filled to match the one next to it because i used that column in the formula
@@easy_sheets You hurt my brain with all of this knowledge ☺️ Thank you for responding to clear it up for me 👏🏾
unique formula doesn't work on my excel but it works on Google spreadsheet
How can you add the count with a pivot table?
which do You Use Excel version?
Pro tip alt+F4
How to change another check
😊
Would this work for a wedding invite list? I need to send invites but some households have more than 1 person. I wanted a count for who has RVSP'd, who's pending and who said no.
How if i want make more then 1 type fo example tea, then split to hot and ice tea
You can do the same easier than pivot table
Does it take less resources?
So. Here’s a wild request, if I wanted to list how many times the number 1 appears in a series of numbers ranging from 0-364 with most numbers spaced out every 5 but there are a fair amount of deviations like 5, 10, 15, 20, 25, 28, 29, 30, 31, 35 all the way to 364
Can Excel tell me how many times 1 appears? Or how many times 3 appears? Rather
How many times each digit 0-9 appears?
Not quiet sure, but have you try conditional formatting tab?
Maybe a custom function on the Ctrl + 1 pop up.
What is an absolute reference?
Why to break our head with these formulas. when we can use Pivot table.
How do you make the single column of data into a pivot table with the count?
I have 1 to 100 number within range of 20 to 300
I need values below 50
I will able to count how many number
But i need individual numbers
What does absolute function do?
Prevents the range values from shifting when you copy the cell, it's not actually needed in this example though just makes it easier
@@ParadiseDB7it is needed as the range of countif would shift by one per row
@@suprguy again, it's not NEEDED it just makes life easier.
Dragon you shouldn't be commenting, you don't know excel
Or we can do just one pivot
Notes: You can't you unique in google sheets
Can you please help me? Mine's just showing #NAME? when i try the =UNIQUE() formula. Pls help.. and is this possible also if i need to count the number of time it appears from a different sheet? Thank you
What is the purpose of making the formula absolute or press F4? Please someone answer. Thank you.
If you don't use F4, when you drag down the COUNTIF function, it will begin the range on the row the criteria is on. I.E: Coke is on D4, and without F4, your COUNTIF will also start of B4... but you want it to always start counting on B3.
@@mattwatson5408 Got it. Thank you for the explanation.
ee pivot table?
Дайте эти функции на Русском экселе! Прошу(
P.s. Про ВПР я знаю
How would you do it without the UNIQUE() function?
Pivot table
Use a pivot table. If it's quick, I'll use a formula, but if I want to manipulate the data and add slicers I'll use pivot tables.
But the Drink column will probably change over time. Unique will automatically update, but the counts column will not add a row automatically. Got a trick for doing that besides an if empty return empty else count?
Also the countif works on small spreadsheets, but get a few hundred thousand rows and it gets very slow.
In the first argument of your Countif, use OFFSET(first cell of your list, 0, 0, COUNTNA(whole column of your list),1).
In terms of speed, maybe you should try a Pivot table. Selects the whole column, then remove blanks in the Pivot Table Options.
I want to do new entry. Will it change the unique and countif entry figure. How can do this please anybody help me
Select one of Column B in the cart then Ctrl+T.
Table function is activated.
If you input new data B33, automatically reflects unique formula result without modifying the formula.
Or, copy and paste into chat GPT and ask it
Because not apply formula my Excel version
Why can't we use a pivot table ? It's also equally efficient
You definitely can! Each have their use cases. Functions can be helpful when you need the data for an argument in another function when creating custom calculations.
@@easy_sheets thanks buddy
alg
Nomas que no domino el speakinglish...
Why Don't you filter
Too fast
thanks for this method, i will use this to identify some duplicates 🫡
Why is d7 and d8 not the same as b7 Nd b8??