COUNTIFS function in Excel with dates by Chris Menard

Рет қаралды 80,383

4 жыл бұрын

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
○ KZfaq 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!

Пікірлер: 68

@MohamedAmr-n8q 19 күн бұрын

You are a life saver! , Thank you so much

@keatitan6648 Жыл бұрын

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.

@ChrisMenardTraining Жыл бұрын

You're welcome!

@TheUnknownGamer1162 4 ай бұрын

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.

@davidbell8819 3 жыл бұрын

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!

@ChrisMenardTraining 3 жыл бұрын

You're very welcome! Thanks, David.

@rafasoares7 2 жыл бұрын

omg, you saved so many hours of my life. thank you!

@ChrisMenardTraining 2 жыл бұрын

Glad to help. Thanks, Rafael.

@pacman.96 Жыл бұрын

THANK YOU BOSS!

@ChrisMenardTraining Жыл бұрын

Glad to help.

@63wpeteronyegbule68 3 жыл бұрын

You're the BEST. LIFE SAVER!

@ChrisMenardTraining 3 жыл бұрын

Glad to help!

@waqasshamas 3 жыл бұрын

You saved my day, Allah bless you with real worth

@ChrisMenardTraining 3 жыл бұрын

Thank you.

@Natalie-gn6nt Жыл бұрын

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 😁😁

@alanh.711 3 жыл бұрын

Great work. The date formula explanation was brilliant. Used that with sumifs formula.

@ChrisMenardTraining 3 жыл бұрын

Thank you. You have a good point about the dates, they work with Sumifs also.

@ckaz7678 Жыл бұрын

great video, thanks for this. And thanks for showing your least favorite method, it's the one I was looking for!👍

@ChrisMenardTraining Жыл бұрын

You are welcome.

@shiffamohammed5818 2 жыл бұрын

very good content presented nicely. Thank you so much.

@ChrisMenardTraining 2 жыл бұрын

You are most welcome

@keiilala 2 жыл бұрын

Well-explained! Thank you!

@ChrisMenardTraining 2 жыл бұрын

You're welcome!

@jayjoelymadelo4150 3 жыл бұрын

Its really helpful. Thank you so much

@ChrisMenardTraining 3 жыл бұрын

Most welcome!

@CPAJayhawk 9 ай бұрын

Thank you

@ChrisMenardTraining 9 ай бұрын

Welcome

@MEETMEKANNAN Жыл бұрын

Thanks Chris

@saaduddin6781 3 жыл бұрын

Thank you, did not know that you had to concatenate to do dates that way.

@sovannetmeas 2 жыл бұрын

Thank you so much

@ChrisMenardTraining 2 жыл бұрын

You're most welcome

@mahmoudbahaa3062 2 жыл бұрын

brilliant video

@ChrisMenardTraining 2 жыл бұрын

Glad you enjoyed it

@Pankaj-Verma- 2 жыл бұрын

It is always so fantastic to watch you, Chris. Thanks a lot, sir.

@ChrisMenardTraining 2 жыл бұрын

Thank you!

@joshualeonricardo Жыл бұрын

THANK YOU!!!

@itsmetinnie1685 3 жыл бұрын

great video sir

@ChrisMenardTraining 3 жыл бұрын

Thank you Christine

@itsmetinnie1685 3 жыл бұрын

@@ChrisMenardTraining thanks for the notice sir. More videos please

@dhruvikghadiya9412 Жыл бұрын

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.

@thtgrldiana6388 2 жыл бұрын

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. 🙂

@jollipetdrive3660 8 ай бұрын

Hi Chris, how do you do a countif function for if the (exact auction date = exact sale date = count 1)

@ChrisMenardTraining 8 ай бұрын

Add a helper column and use A2 = B2 then do the COUNTIF Function drive.google.com/file/d/1VMmBfCfuKBHQOw5TtP3upHxtErnd17Ef/view?usp=drivesdk

@user-uj5oe3vq9e 5 ай бұрын

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

@sourrabhaggarwal8887 Жыл бұрын

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.

@crisjohnjimenez5905 3 жыл бұрын

What if I want to count a product within the day

@kellyw4922 11 ай бұрын

How do I find a total number of cells that have dates in it, but excluding any cells that read "not open"?

@ChrisMenardTraining 11 ай бұрын

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.

@matthoyle951 Жыл бұрын

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?

@StevieC82 Жыл бұрын

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?

@ChrisMenardTraining Жыл бұрын

Use a PivotTable Here is a screenshot drive.google.com/file/d/1Jh_YTdAndrxx1TcamVu8h8kZl62p4OGY/view?usp=drivesdk

@stevenrichards9272 2 жыл бұрын

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?

@ChrisMenardTraining 2 жыл бұрын

=today()-10 will give you 10 days from the current date.

@stevenrichards9272 2 жыл бұрын

@@ChrisMenardTraining thank you

@user-pt7cu5cs4e 8 ай бұрын

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?

@shadbir Жыл бұрын

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..

@ChrisMenardTraining Жыл бұрын

You could use Countifs for that, but I would filter by date in that example.

@CategoricalImperative 6 ай бұрын

Anyone else have the habit of forgetting the comma, then spending FOREVER trying to figure it out? lol

@yoyara97 2 жыл бұрын

What if I want to know how many a's are between a date range?

@syedashfaq7245 Жыл бұрын

How can I count only cells which is just before and after a particular cell

@vangieevangelista9488 3 жыл бұрын

I tried the formula why is it the returning value is a date instead the count

@ChrisMenardTraining 3 жыл бұрын

Can you change the formatting of the answer? See if that works. Let me know.

@amyjain97 2 жыл бұрын

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

@Mr_Melo 2 жыл бұрын

Can I contact you to help me create a formula?

@amolarunparihar9846 2 жыл бұрын

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