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.
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?
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 🙏
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 ? 🤔🤔
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?
The "F4" bonus! 🤯🤯🤯
You have solved a question I have had for months. Thank you
Wall Street could use your skills. Fantastic!
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.
F4 to make an absolute reference. What a prize!
I love your videos!
This is cool. I've been using pivot or remove duplicate the wholetime
A million thanks!!!
Love you bro, helped me a lot
I would love to see tutorials like that also for LibreOffice Calc.
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.
Awesome, video! Tks, bro!
Super! Did not know about this one!!!
Totally what I needed today. 👍 Thanks
Thankyou so such ❤️❤️❤️
Seems like a quick pivot would be faster with a little list like this.
I ha to do this last moth and I was going crazy! Thank you so much for this videos you post
Hot damn I've been copying over my collumn and removing duplicates that's awesome
Dude. Thank you.
Excellent 😊
I need to practice this is so important
this good for spare part
In new excel you can map a lambda against the unique array all inside a single cell, spill into the two columns with hstack ^^
You’re a life saver❤
Everytime I see your video, my mind is blown. This is amazing stuff¡
This is a good one
I'm starting to understand why excel competitions exist
Perfect
Damn I need to reup my Excell subscription. I can use all of these tips.
Oh dang, I did that the slow way like 10 times just today 😂
I am so not going to remember this when I need it. I'll just have to go make a pivot table😊
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! 🖤🦇
❤
Those $ to keep formulas from moving, perfect, thank you lol
Thanks
👍
super Bro :)
I would use Pivot for this purpose
Pivot tables! Pivot tables! Yes, indeed!
Thanks Sir
Wow
No need to highlight, just press F4.
I literally could have used unique function yesterday
What’s the alternative for F4 in Mac?
How to do the other way round, which is to convert the right table to the left one?
or create a pivot...
wait, what? highlight then press F4?
.... the hero I didn't know I needed.
It's asking # name
How to change another check
Thanks, you helped me last minute saving my life. Thank you very much ❤ ⁴⁴²⁴
Table + pivot table = done
How can you add the count with a pivot table?
Can you do the same thing with QUERY()?
which do You Use Excel version?
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?
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 🙏
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
How if i want make more then 1 type fo example tea, then split to hot and ice tea
unique formula doesn't work on my excel but it works on Google spreadsheet
😊
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.
Does it take less resources?
Pro tip alt+F4
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 👏🏾
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.
You can do the same easier than pivot table
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?
What is an absolute reference?
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
ee pivot table?
Or we can do just one pivot
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 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
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.
Notes: You can't you unique in google sheets
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.
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.
Because not apply formula my Excel version
Or, copy and paste into chat GPT and ask it
Дайте эти функции на Русском экселе! Прошу(
P.s. Про ВПР я знаю
alg
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
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??