How To Count Colors with an Excel Function | Count Colored Cells

how can we count callers in Excel this is what you guys in today's video with a practical example we're gonna use a function to count colors in Excel so with the first question that you maybe you have in mind is why should I for one reason should I count colors in Excel I'm gonna give you a good reason so for example here with this radical situation where I have here a sales report with the date SKU the item and the quantity the employee what uh was responsible for this report just highlight here for me the items that just sold the most with this green color and the items that I just didn't solve the well with red color for example anything between we have here the blue color the yellow one and the orange one and so on so let's say instead of having this visual effect I want to have something more precise so instead instead of using colors I want to use numbers I want to use values itself so here I I have all the colors that the employee used before orange red these two types of blue uh yellow and green so in the next column I want to count how many times the orange colors just appeared before how many times the red one appears so to do it Excel does not have a exactly function to help us with this particular situation so we're gonna need to create our own function using VBA but it's pretty much simple okay don't need to worry about that we can start here clicking the developer tab and if you do not have this developer tab in your Excel do not worry just go let's say to the Home tab and in any blank spot here click with the right click and then go to the customize option okay now with this new windows that is going to appear for you you can change here popular commands to let's say all tabs for example and here we can find the developer tab you can click clicking add and then you can click here okay now you are gonna have here the developer tab in into your Excel now you can go here Hereford Force 2 that is Visual Basic you can click here and within this window you're gonna create our own VBA function or to make it faster and easier you can just scope and paste the link that are gonna leave to you okay so I'm gonna go here the first thing insert just before we go and paste the code so insert Tab and then I go to module I'm going to click here and now we can just go and paste Ctrl C Ctrl V or code or script here into VBA okay now we can just close all this window right here and do it then basically so if you just click here in the first cell to count the colors you're gonna see that equal sign count call or the function that we just created so double click here the first thing that I need to select here is the range that I'm looking for so the range that I'm looking for is all this range here in the quantity so we're going to select everyone that is here and then I'm gonna come back here one part step here is to press the F4 key to make sure we can lock the reference because when I just click hold and drag down the function to make sure all the rows contain the same function I don't want to lost my main reference okay my lookup values so I want to stay with this range in the same position okay I don't want you to drag down with the function so I press F4 key and then comma and now I select the color that I want to count so I'm going to start here with this orange color I can close prevents and then press enter okay so the count color function returns from from here to 8 as a result so that way I know that the orange color just appeared before eight times if I click here in the down right corner of the cell click hold and drag dial as you guys can see I got the result for all the colors that I have and of course you can do it with another example so let's say here I want I want to add here a new sheet and then I want to paint here just a couple of cells let's say yellow I'm gonna paint yellow again one more time and then I'm gonna paint here let's say uh a red one okay so I want to count how many yellows and red I have I can just hear Union cell just paint one in red and another one in yellow so like this for example and now we can use the function equal sign count color double click it to count for me all the colors that I have and instead of just select the specific range let's say the way I can select a large range for example like this and then I can press F4 key to make sure I lock the reference comma and then I'm going to count first I'm going to start here with the red color enter and yeah the red color just appeared one time then if I click here hold and drag down as you guys can see the account color function just returned from if one and three so yeah it's correct so we're done I hope you guys you enjoyed this video if you have any questions any doubt if you want to learn more about VBA to learn to understand when and how I use any value that I have here any function how can I create a script using VBA to automate some boring tasks in Excel let me know comment down below and I see you tomorrow as everybody has a new video I see you there

Channel: Jopa Excel

Views: 162,744

Keywords: how to, how to excel, jopa excel, step by step, spreadsheet, function, formula, excel formula, ms excel, microsoft excel, vba, visual basic application, count colors excel, count excel, excel vba, vba function, excel function

Id: fwSIIDm08fo

Length: 4min 56sec (296 seconds)

Published: Fri Jul 21 2023