COUNTIFS function in Excel with dates by Chris Menard
HTML-код
- Опубликовано: 13 янв 2020
- To count the number of occurrences for a range or multiple items use the COUNTIFS function. When it comes to a date range, whether is it for a month, quarter, or any date range, the COUNTIFS function is what you need. Examples:
How many orders did we get for Quarter 1? That is the COUNTIFS function since it is between a range of dates.
How many times was product A ordered for customers in Australia?
How many orders did we have between January 1 and March 31?
The COUNTIF function, which I covered in this video, will handle one criterion. COUNTIFS handle multiple criteria.
TimeStamps:
01:07 Countif function
02:30 Countifs with text.
03:50 FormulaText function
04:37 Countifs with dates method 1
07:40 Countifs with dates method 2
10:20 Countifs with dates method 3 using EOMONTH function
In this video, I'll show three methods for doing Countifs between dates.
Method 1 is typing the date in the function
Method 2 is typing a Start and End date and using concatenate in Excel to join COUNTIFS with the cell reference.
Method 3 is using the EOMONTH function in Excel.
#msexcel #excel #exceltraining #countifsexcel #excelcountifs
Chris Menard's website:
chrismenardtraining.com
Upcoming live event:
Chris Menard will speak to the DeKalb Chapter of CPAs, part of the Georgia Society of CPAs on February 25, 2020. Menard's presentation will be Excel Analysis and PivotTables.
chrismenardtraining.com/event...
And make sure you subscribe to my channel!
- EQUIPMENT USED --------------------------------
○ My camera - amzn.to/3vdgF5E
○ Microphone - amzn.to/3gphDXh
○ Camera tripod - amzn.to/3veN6Rg
○ Studio lights - amzn.to/3vaxyy5
○ Dual monitor mount stand - amzn.to/3vbZSjJ
○ Web camera - amzn.to/2Tg75Sn
○ Shock mount - amzn.to/3g96FGj
○ Boom Arm - amzn.to/3g8cNi6
- SOFTWARE USED --------------------------------
○ Screen recording - Camtasia - chrismenardtraining.com/camtasia
○ Screenshots - Snagit - chrismenardtraining.com/snagit
○ RUclips keyword search - TubeBuddy - www.tubebuddy.com/chrismenard
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week! 
Хобби
Thank you for explaining it in such an easy way. I was working on data but was confused about using Countifs function when working with dates. But your video helped me out. Thank you so much, Chris.
You're excel-lent! Thank you so much, I've spent ages reading through so much jargon to finally get to your brilliant video. Saved me!
You're very welcome! Thanks, David.
Thank you so much Chris! This is the only place on the internet I could find the method you used! Hope you are doing well.
You're welcome!
Thankyou so much for posting this, you are a great teacher and made it so easy to understand!!
I never got to learn any of this in school and am currently trying to self learn some functions for a project I’m doing. Right now I am so happy because I just learnt to do something I thought was outside of my capabilities and it’s thanks to you 😁😁
You're the BEST. LIFE SAVER!
Glad to help!
Great work. The date formula explanation was brilliant. Used that with sumifs formula.
Thank you. You have a good point about the dates, they work with Sumifs also.
Well-explained! Thank you!
You're welcome!
very good content presented nicely. Thank you so much.
You are most welcome
omg, you saved so many hours of my life. thank you!
Glad to help. Thanks, Rafael.
THANK YOU BOSS!
Glad to help.
You saved my day, Allah bless you with real worth
Thank you.
Its really helpful. Thank you so much
Most welcome!
Thanks Chris
Thank you, did not know that you had to concatenate to do dates that way.
great video, thanks for this. And thanks for showing your least favorite method, it's the one I was looking for!👍
You are welcome.
THANK YOU!!!
Thank you so much
You're most welcome
brilliant video
Glad you enjoyed it
Thank you
Welcome!
Hi, Thank you for your knowledge sharing! I am managing help desk tickets. I want to perform a calculate function that tells me how many tickets were created on a specific day. I want to show this calculation for each calendar day where an incident was created. I have several columns: I have an INC number column, Date created, Date closed. the date columns are formatted as 01/01/2021 (mm/dd/yyyy). I also want to perform this calculation/function in a seperate worksheet. It seems counting date occurrences are not as straight forward as other sum/count functions. The basic Count/countIf functions are not working and I couldn't figure out how to implement your solution either... please help. 🙂
It is always so fantastic to watch you, Chris.
Thanks a lot, sir.
Thank you!
great video sir
Thank you Christine
@@ChrisMenardTraining thanks for the notice sir. More videos please
Thanks a lot for sharing amazing lesson about the excel formula.
May i please request you to share the formula with same example that how to count AU in collumn E if in collumn D has mentioned date has January month.
Hi can you please tell me how can we calculate number of customer for a particular date suppose I want to calculate number of customers added on the 2/1/2021 using count if function.
I have a table and want to count the number of cells in a column with specific text but only if the dates in another column are between 2 dates - basically if it says "Booked On" and is in January or February and so on. Any ideas?
I have been looking to find out how I can countifs dates grater than "today" based on 2 criteria 1st: country code equal to GB and 2nd criteria: product equal to C
How do I find a total number of cells that have dates in it, but excluding any cells that read "not open"?
Do the cells that have dates, also have numbers? For example, B2: B70 has dates and "not opened" in them. You could just use =COUNT(B2:B50) to find the cells with dates. Now, if some of them have numbers, that would throw it off.
Hi Chris, how do you do a countif function for if the (exact auction date = exact sale date = count 1)
Add a helper column and use A2 = B2 then do the COUNTIF Function drive.google.com/file/d/1VMmBfCfuKBHQOw5TtP3upHxtErnd17Ef/view?usp=drivesdk
What if I want to count a product within the day
What if I want to know how many a's are between a date range?
Hi, I know this is 3 years old however when I follow the date calculations, for some reason it doesn't add up the end of month (31/05/2023) for example, so the total is 52 on my master sheet, but the total is coming up 49 because 3 of those are dated 31/05/2023. I've followed step-by-step but it's still not adding the last day of the month. Any advice?
I have a table with a range of dates in one column and names in another. how can I count the number a name appears for each month?
Use a PivotTable Here is a screenshot drive.google.com/file/d/1Jh_YTdAndrxx1TcamVu8h8kZl62p4OGY/view?usp=drivesdk
Hi, great video.
I am wondering if you can help with a problem I have. I want to wrote a formula which will count the last ten days from the current date where the current date is dynamic and changes each day.
I want to do this so that I can record the formula as a macro
Can you help with this?
=today()-10 will give you 10 days from the current date.
@@ChrisMenardTraining thank you
How can I count only cells which is just before and after a particular cell
Hey Chris, how do we get the numbers for particular qtr, like for any current qtr and this should also change as and when we move to new qtr
Dear, how to find a specific value from a date range.. Like, I want to find the total number of "Incoming calls" value from 11th Jan to 20 Jan..
You could use Countifs for that, but I would filter by date in that example.
I tried the formula why is it the returning value is a date instead the count
Can you change the formatting of the answer? See if that works. Let me know.
Can I contact you to help me create a formula?
Anyone else have the habit of forgetting the comma, then spending FOREVER trying to figure it out? lol
How to find out in first column Cell A1 - R100 ,B1- Match,
Cell A2 - R100 , B2 - Not Match
Cell A3- R200, B3 - Not Match
Cell A4 R300, B4 - Not match
Cell A5 R300,B5 - Not match
How to find out only Not match result by formula when there all results are not match
Thank you
Welcome