@@Computergaga Is it possible to rank items based on more than one criteria which references different cells? For example, I have a list of times from a workout and if the athlete doesn't get under the time cap, his/her reps are noted instead of a time. I would like to rank these independently if possible.
Yes, absolutely. The COUNTIFS function can take multiple criteria. So I think you need a column to identify as you enter peoples results whether they made the time limit, or didn't and will be ranked by reps. You can then rank the time ones, and the rep ones like I did with London and Auckland in the video.
Thank you. I actually have something for duplicate rankings using COUNTIF or COUNTIFS th-cam.com/video/QFdsGGlSmrU/w-d-xo.html This can be added to the ranking formula.
Hey, Thanks for sharing! I am stuck with one case. so, let's say from your data example London has two records with the same values 54 Euros. In that case, I want to rank them in order say the first record as ranked 3 and the next record with London and value 54 the rank should be 4. Can you help me with that formula?
Thank You, this help me a lot. The ultimate prize is to get DAX and or Power Query M to it. I have a Vehicle Allocation file, inside there a single vehicle can do multiple trips. With a ranking on the 1st to last trip
Hello..how can we rank an item ie "Location" based on aggregate of its value ie by "sales" . say aggregate sale for London is 1774 .So all London rows should be ranked as 1 then take the total of Chicago and rank all Chicago as 2 based on its total sales. accordingly .Finally how can get a top 5 ranking updated automatically based on this. Appreciate your help on this
Your best bet would be to either use a PivotTable which can do the aggregations and rank for you. Or use formulas such as SUMIF to firstly get the aggregations for each city, and then on those results follow the approach in the video.
Computergaga Good to know. Thanks btw. I am also looking for more VBA programming. It helps a lot. However, till now I only use macros by recording them. Not much customization in it though.
That is the next step for you I guess Keval. I have videos on VBA on here and an online course. You need to start trying to enhance what you have and you will learn VBA quick.
Nice jop Is there formula can give me same result of below =countif(b1:b9,b1:b9) Because this doesn't handle the array in formula's bar, its just for ranges .thanks
Computergaga Assuming i had =mid(b2:b9,{1,2},1) Now i need to count each parameter in result of mid() So when i use =COUNTIF(mid(),mid()) it cannot handle array of mid(). Countif it can handle the ranges. So i try to find smart formula it can give me that result in formula's bar when i press F9 button ={3;3;1;3;3;3;1;3} I hope that was helps =
Sorry Lord Man still not sure. Difficult without a spreadsheet. I don't understand why an array is being used for the Start_Num question of MID either. What and why you are counting. It might be worth looking into SUMPRODUCT. A brilliant array function that can probably help you do what you need.
Computergaga last try 🤗 If u put the next formula in ur formula's bar in same this sheet in the video, and u selected the formula in formula's bar then u have press F9 button =countif(b2:b9,b2:b9) U will get that ={3;3;1;3;3;3;1;3} is this right? Now i want to reach to the same result without using countif() function Just like that no more no less
Yes that is right. It will count the number of occurrances of London, Chicago etc in that range. There will be other ways like SUMPRODUCT. However that works, why you need another way? If you select a range of cells such as D2:D9. Put that formula in the formula bar and do Ctrl + Shift + Enter it will fill out the cell D2:D9 with those result 3, 3, 1, 3 etc. If that is what you need instead of pressing F9 to get the result.
Hello Alan. I have a question for you. I need to count column C 1 to 10 when i sort column A . How do i do? Ex. Column A Column B Column C Item Name Count_Item 10 aaaaa 1 5 aaaaa 2 6 aaaaa 3 100 aaaaa 5 105 aaaaa 6 1110 aaaaa 7 104 aaaaa 8 153 aaaaa 9 1010 aaaaa 10 1012 bbbbb 1 55 bbbbb 2 666 bbbbb 3 10010 bbbbb 5 1051 bbbbb 6 11101 bbbbb 7 1041 bbbbb 8 1530 bbbbb 9 2000 bbbbb 10
This is gold!
I'm sure most people are unaware just how powerful Excel really is.
Thanks.
Thank you.
@@Computergaga Is it possible to rank items based on more than one criteria which references different cells? For example, I have a list of times from a workout and if the athlete doesn't get under the time cap, his/her reps are noted instead of a time. I would like to rank these independently if possible.
Yes, absolutely. The COUNTIFS function can take multiple criteria.
So I think you need a column to identify as you enter peoples results whether they made the time limit, or didn't and will be ranked by reps.
You can then rank the time ones, and the rep ones like I did with London and Auckland in the video.
One of the greatest Excel Channel on TH-cam.
Thank you Pankaj.
One of the best explanation I've heard! Thank you.
Thank you Amy.
Well explained. Amazing!
Thank you very much.
Great video - please include something for duplicate ranking.
Thank you. I actually have something for duplicate rankings using COUNTIF or COUNTIFS th-cam.com/video/QFdsGGlSmrU/w-d-xo.html
This can be added to the ranking formula.
Thank you Alan, very useful video
Cheers
Mohideen
Thank you Mohideen.
Thanks mate for this superb video
You're very welcome Syed.
Thank you this is perfect for what I needed.
Great to hear. Glad to help.
Exactly what i needed, amazing! Thank you!
You're welcome Dennis.
This is genius, thank you so much!
Thank you. Glad it helped.
Superb sir👌
Thank you, Nagesh.
nice video and please share workbook with your videos
Hey, Thanks for sharing! I am stuck with one case. so, let's say from your data example London has two records with the same values 54 Euros. In that case, I want to rank them in order say the first record as ranked 3 and the next record with London and value 54 the rank should be 4. Can you help me with that formula?
Thank You,
this help me a lot.
The ultimate prize is to get DAX and or Power Query M to it.
I have a Vehicle Allocation file, inside there a single vehicle can do multiple trips. With a ranking on the 1st to last trip
Thank you, Anthony.
Thank You very clear, can you please help me here, when I have the same but I have duplicate number and I don’t want it to skip numbers.
Thanks sir, but now how if i want to add 1 criteria? Example rank per month per location from sales? Thanks
Very Very nice
Thank you.
This is great sir but how can we implement DENCE RANK !!!!!! if anyone has any solution please comment!
thank you
You're welcome Ezel.
Pls help me to eliminate duplicate ranks
Supurb!!!!
I am wondering why such logics cant I find.😄
Thank you!
This is not working if the numbers are repeating and we want a same rank for the repeating values.
Hello..how can we rank an item ie "Location" based on aggregate of its value ie by "sales" . say aggregate sale for London is 1774 .So all London rows should be ranked as 1 then take the total of Chicago and rank all Chicago as 2 based on its total sales. accordingly .Finally how can get a top 5 ranking updated automatically based on this. Appreciate your help on this
Your best bet would be to either use a PivotTable which can do the aggregations and rank for you. Or use formulas such as SUMIF to firstly get the aggregations for each city, and then on those results follow the approach in the video.
Good logic...
Thank you Keval.
Computergaga I must admit that your excel knowledge is excellent. I bet you can do programming very well too.
Thank you. I do a little bit. I teach Excel VBA.
Computergaga Good to know. Thanks btw. I am also looking for more VBA programming. It helps a lot. However, till now I only use macros by recording them. Not much customization in it though.
That is the next step for you I guess Keval.
I have videos on VBA on here and an online course.
You need to start trying to enhance what you have and you will learn VBA quick.
Nice jop
Is there formula can give me same result of below
=countif(b1:b9,b1:b9)
Because this doesn't handle the array in formula's bar, its just for ranges .thanks
I'm not sure what you are trying to do Lord Man.
Computergaga
Assuming i had =mid(b2:b9,{1,2},1)
Now i need to count each parameter in result of mid()
So when i use =COUNTIF(mid(),mid()) it cannot handle array of mid().
Countif it can handle the ranges.
So i try to find smart formula it can give me that result in formula's bar when i press F9 button ={3;3;1;3;3;3;1;3}
I hope that was helps
=
Sorry Lord Man still not sure. Difficult without a spreadsheet.
I don't understand why an array is being used for the Start_Num question of MID either.
What and why you are counting.
It might be worth looking into SUMPRODUCT. A brilliant array function that can probably help you do what you need.
Computergaga last try 🤗
If u put the next formula in ur formula's bar in same this sheet in the video, and u selected the formula in formula's bar then u have press F9 button
=countif(b2:b9,b2:b9)
U will get that ={3;3;1;3;3;3;1;3} is this right?
Now i want to reach to the same result without using countif() function
Just like that no more no less
Yes that is right. It will count the number of occurrances of London, Chicago etc in that range. There will be other ways like SUMPRODUCT. However that works, why you need another way?
If you select a range of cells such as D2:D9. Put that formula in the formula bar and do Ctrl + Shift + Enter it will fill out the cell D2:D9 with those result 3, 3, 1, 3 etc.
If that is what you need instead of pressing F9 to get the result.
How do you deal with ranking positive and negative values?
If you want them to be treated equally you could use the ABS function to remove the sign.
sir how to takle with duplicate values
Sir how to exclude #N/A errors in rank??
this doesn't work if the value of London is same in different date. then it omits the number 2, 3,4 and so on.
Hello Alan. I have a question for you. I need to count column C 1 to 10 when i sort column A . How do i do?
Ex.
Column A Column B Column C
Item Name Count_Item
10 aaaaa 1
5 aaaaa 2
6 aaaaa 3
100 aaaaa 5
105 aaaaa 6
1110 aaaaa 7
104 aaaaa 8
153 aaaaa 9
1010 aaaaa 10
1012 bbbbb 1
55 bbbbb 2
666 bbbbb 3
10010 bbbbb 5
1051 bbbbb 6
11101 bbbbb 7
1041 bbbbb 8
1530 bbbbb 9
2000 bbbbb 10