How to use COUNTIF and COUNTIFS in Microsoft Excel
ฝัง
- เผยแพร่เมื่อ 26 ม.ค. 2020
- Learn how to use COUNTIF and COUNTIFS to count the number of cells that meet a criterion or multiple conditions; for example, to count the number of times a particular city appears in a customer list.
In this video, I run through the following:
1) Count how many times a certain string or number appear in a list
2) Count how many times multiple items appear in a list
3) Count how many items are greater than or less than a certain value
4) Count how many items are between certain values
5) Count how many items do not match certain values
6) Count how many items begin or end with certain characters
7) Count how many items match certain conditions across multiple columns
Lastly, I cover how to use Excel for free on the web. Excel on the web supports the COUNTIF and COUNTIFS functions.
Here's the sheet I used if you want to follow along:
1drv.ms/x/s!AmxrofZZlZ-whId2V...
Watch more Excel tutorial videos: • ❎ How to use Excel
- Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com
As full-disclosure, I work at Microsoft as a full-time employee. - วิทยาศาสตร์และเทคโนโลยี
🏫 Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com
⏭ Watch next - playlist with all my free tutorial videos on how to use Excel: th-cam.com/play/PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY.html
Kevin, thanks for the post but the link opens into the list of your how to for PowerPoint. Can you post the correct link to the Excel videos?
I`d like to see your list of Excel videos as that`s what I`m learning at the moment, thanks (11/01/2021)
Kevin, please post the correct link to your list of Excel videos.
Sorry about that! I’ve updated the link.
Your'e the best❤...your videos are my go to for everything I need. Thanks so much!
This has been the most easy to understand COUNTIF/S tutorial for BEGINNERS like me.
Very helpful, thanks!
I paid so much money to a course through a local university to learn Office and I end up on your videos because you explain it so much better than they do. Thank you!
I spent hours trying to figure out how to get a formula like this to work for the data I was trying to organize and this was the magic video that stopped my search. THANK YOU!
You're the best because 'when you teach something you cover each and every aspects of the topic'. Thank you ever so much
I really really hope Microsoft was paying you extra for these videos bc I know a lot more people are using excel bc of you. Everyone in my office watches your videos from old to young either to brush up or to learn something new. As always, thank you for your videos Kevin and not just for Microsoft products but all the programs/system you teach us how to use (I literally learned how to edit videos from your other videos too)
Another quite simple and direct and accurate video! My ADHD thanks you, good Sir, for a job well done!!!
I am a brand new accounting student. Literally in my first unit, in my first semester. You sir are a life saver! Thank you so much, you have a new subscriber :-)
With the help of this video I solved my headache worksheet. Thanks alot Mr.Kevin Stratvert. God bless you.
I am addicted to your videos because I became very confident that after watching them, I can call myself an expert in what I just learned. Thank you so much, Kevin! 👍👍👍
Great to hear!
Kevin, this video helped me so much with a project that I'm doing right now. No one else could help me with the count question! Thanks so much!!!
Thank you - I support multiple programs, and have a spreadsheet for the personnel I'm responsible for with their program & location noted. But couldn't figure out how to set up running tallies of different data points for the inevitable "need the info yesterday" data calls without getting a !SPILL error.
I'd figured out how to use COUNTIF, but not how to combine multiple programs or locations without having multiple queries then SUM those cells. Using your walkthrough, I just set up 4 different COUNTIFS that have info I am often asked for, so I can now respond to "Need NOW" emails as fast as I can pull up the spreadsheet.
You've saved me a lot of time & stress, thank you.
I AM NEW TO EXCEL, I DID NOT ONLY LEARNT COUNTIF AND COUNTIFS BUT ALSO THREE MORE USEFULL THINGS I DID NOT KNEW AND TEACHING STYLE IS BEST.
Hey man, thanks a million for the help. I'm working on some modules for work and the instructions that we're given are completely vague and lacks proper directions. THIS was insanely helpful. I just hit the subscribe button. Thanks again.
Man, you just saved my week, didn't know about that ">"& to reference a cell.
I didn't know that the "=COUNTIFS" function requires 2 sets of condition for it to work. This video helped it break it down for me. Thanks alot!
Awesome, thanks for this! 👍🏼 putting together a huge tracking spreadsheet for work and this formula is a life saver
I really like your explanation. Clear and very easy to follow. You help a lot of people how to use and understand excel, keep it up bro.! You are a blessing to many. God bless you!
Brilliant. Clear and straight to the point. Easy to understand. Thank you
You could not ask for this to be any clearer. Thanks!
You just cleared my one month lesson in one video... thank you soooo much.. you saved me😄
Thanks Kevin for your efforts on the countif/s functions... great work and very helpul.
5:00 first check point
6:52 second check point
8:24 third checkpoint
10:55 final lap
FINISHED!
thank you so much for your good lesson!!! very straight to the point and easy to comprehend !
For the red and yellow example, instead of adding 2 countif formula's can I just use countifs instead?
What is the difference between count multiple items and countifs?
This is really helpful. I've been searching how to count how many B if A in two different rows.
THANKSSSS....after checking so many videos, finally I got here what I was looking for!
Year 2024 and it is still helping. Thanks Kevin for the good work!
Perfect! This is exactly what I'm looking for! Thanks a lot. It'll help me creating my tracker 🥰🥰🥰🥰 Awesome
Great video very informative to understand. You're a good teacher. Thank you.
Wow thanks bro, youve literally saved me hours of work. Its not that I didn't know the formula, but I was underperforming it. I didn't know that the "range" could deal with multiple sets of data at once. I was using this function for each row for thousands of samples instead of using the "range" to combine everything and calculate a full table of data in one cell....not sure if you get me, lol but thanks this helped
I really learned a lot from this. Rich examples and great delivery. Thanks for all you do
Thank you Kevin. Liked your countif presentations.Simple and variety of countif , countifs were simple and easy to understand. 🎉
Please feature power query importing and cleaning financial data from SAP, ORACLE; csv, pdf
Thanks so much
I can never forget this tutorial. So helpful
I think out of this whole video the fact that you called the '&' by its real name was, most awesome, I had to look it up.
Thank you Kevin, it really helped me a lot
Kevin! Kevin! Kevin! You are a star. Thanks for this great video
I really learn so much with your videos very well explained, THANK YOU!
Very much interesting & useful also. Keep making this type of videos .
you know how to teach, you made it easy with ease and patience, thank you. will base my search on your channel then:)
Great tutorial Kevin, however, I have single cells in my spreadsheet with multiple numbers that I'm trying to summarize. For example cell E:7 contains the numbers 2,3,5,7,9, 12. I want to ask Excel to tell me how many times the number "2" appears in cell E:7. I think this isn't possible no matter where I put the wildcard asterisk as Excel is reading all my number entires as one single number. ANy suggestions would be appreciated. Thank you.
Thanks for helping as revision for my test tomorrow! :)
Excellent presentation. Could you direct me to a formula to use on an Excel spreadsheet that contains dates & where I need to sort those dates by 60/180/365 days old? Thanks
I need to count actual question marks in a spreadsheet. In my spreadsheet, we use a ? for those who are unknown/undecided. Is there a way to do that.
So for the Countifs function can you use it for more than 2 criteria to look up, such as 3 colums of data?
when using countif, how do you set a range on a large amount of data? can this be done like automatically or do i have to manually select C2-C956
Thanks Kevin, how do count number of instances against a date range on another sheet?
Thanks Kevin, these are some sick function tips
Need to know how to set up an excel that will give you scores for the army physical fitness test. If you look up the grading standards for the army APFT you will see that it will need a lot of formulas. Please help.
Is this possible to look for a posible special character within the range?
You saved alot of my time thank you so much for sharing this information. 💜
Great and infomative video! Very easy to understand. great Job!!!
Do you have a tutorial on using the COUNTIF function to insert rows into a different Excel file/template to copy the source data into it?
Just fantastic! I appreciate your time to make this very helpful video.
This is super helpful. Thanks, Kevin! 😊
Very good video. It would be great to include "extra" tips by mentioning you can select the whole range by using shortcut keys. If the range extends two or more screens click and drag would be a drag (pun intended). Excellent video though. Thans.
Tysm these videos have really helped with revision for my upcoming test! Wish me luck it's tomorrow
wow! very clear explanation! Thanks a lot!
Thanks so much, you save me hours of head aches
You are brilliant. You,be very helpful to for almost 3 years now. 👍😎
So usefull and simple, keep it up man.. greetings from Turkey 😉
at around 12:00 you begin to enter the second criteria for your "countifs" equation, my question is can I use the same criteria range for both criteria? I tried using your exact formula but using "civil" for my first criteria and "structural" for my 2nd instead of ">=400". My total was zero but should be at least five. Any help would be very much appreciated
nice job. this really helped with my spreadsheet for work. i had 2 sheets that didn't balance...i suspected the 300's were the difference...actual error was 299.20 . used the countif to work out one sheet had 41 on, other had 42. not only was this quicker , it was far more accurate than if i'd done it manually. very useful trick as i often get this type of issue. ( having found the big diff, i then zero'd in on the smaller diff of 0.80 soon anyway ! :) )
Good stuff here. Just used this to fix a major problem with accountability on a spreadsheet used by a group for work reasons.
HELP!!!! I am trying to use the countif function and I can't figure out what I'm doing wrong. I have a column, Column N that holds my dates my students completed their job shadow. It's marked as mm/dd/year, ex. 09/08/1972. Rows 1 & 2 have headers and I want the total to appear in Row 2, Column N. I use =countif(N3:N56,"*") and it gives me 0. The number 8 should appear...what am I doing wrong?!
Thank you! That document really helped.
Straight to the point ! Thank you!
Is there a option to reverse the COUNTIFS function? like if I have already done the count if and I wan to reverse it using a formula.
Hello, Can you explain how to do count if based on color, so I want all my cells in white to be counted, and all the ones in yellow to be ignored?
Thanks for this video. Please How can I make a value return to an exact value when the incomplete value is inputted? For example ; I have (32LK500, 43LB24 - I want them to appear as 32LK500PVA, 43LB24PVA RESPECTIVELY even when inputted incomplete or incorrectly?
This awesome. I really loved the "wild card" tutorial on how to count "start with" and "end with". I will be using this feature a lot. My biggest issue is how to copy the formula and paste to multiple columns without changing the formula. I always have to do redo the formula for each column. I'm teaching myself how to do metrics and I work with a company that have 32 groups and always have to show results for each month or for multiple criteria.
After you copy the formula, hit the "ESC" key, then paste the formula in another cell.
Thanks
I discovered that the COUNTIF function doesn't count a cells number if that cell is generated via an underlying formula instead of just the number typed directly into the cell. Is there a way to get the COUNTIF function to count those numbers generated via a function?
How can I eliminate the #value error in countifs with multiple criteria
Thank you. Please share all the IF Functions in excel if possible. Thanks again.
can I use a formula to define the range for the function? ie. =COUNTIF( FORMULA, CRITERIA)
Hi Kevin,
I reaaly appreciate this tutorial but is it possible to use the if formula to count how many in a list end with same last three digits. Ex. I want to find out how many reservations with the same last three digits occurred on any given day? Is that possible?
Does * works with other formulas? Whats the difference between with “” and not having it? I only use “” when using iferror command
Great tutorial. I tried using wild char * in IFS, does not work. any settings required. Thanks
Hello Kevin I hope you can help me out I did a count if on my sheet using =countif(A3,"160L") this worked but I have D3 that I want it to add for example if you put 4 it would populate 160L column plus add 4 how can I achieve this?
Thanks Kevin, good job
I have a question. If you have a list and there is a list of names and you want to go through and use the =countif to see how many times a name appears in a list of 5,000 how would I do that?
You're a real hero dude. Thank you
Thank you, well explained...Now to put in to practice.
Thank you for your precious sharing.
Hi Kevin, awesome tutorial thank you! Can you help me with this; I have a large spreadsheet of thousands of separate rows of clients. In it, Column C is First Name, Column D is Surname, Column H is date of birth. Column CM is the date they had a service. Some clients have had this service twice and appear twice in the spreadsheet, with the date of service being different. I want to find the number of clients that have had their second service within a particular date range. Can you help please.
i have a file of excel and in a a row i put "v" i different colums and under "V" i put "x" and "H" i want to count just "X" which under the "v" how to count
Thank you so much for this video Kevin!
How do you count a range?? I want to count numbers between 12 and 22
I can't use countif formula for online excel using two different spreadsheet. Hope you can help
Thank you for the awesome tutorial!
Your the answer to my Hows, thanks alot..
+1 Subscriber and gonna ask my hubby,sisters to follow you... I love you ♥️
I am trying to isolate products where sales are made to less than 3 customers from a vast data set. I am planning to use Countif for separating the SKU list
Tqvm for all the guidance especially for beginner.
Can we use countif for blank cells? if so what should be the formula to be used?
A well effective session.
How did you create those filters?
I'm able to get the COUNTIF function to work in certain cells but not others on the same spreadsheet. Can you help?
What if I want to set up two criteria for the same data set ?
Thank you so much! Such a great video.