Count the number of times a month appears within in a range!
HTML-код
- Опубликовано: 23 июл 2021
- Support Me & More: linktr.ee/benthompsonuk
Do you know how to count the number of times a specific month appears in a range of dates?
This scenario is made really easy with the use of the SUMPRODUCT function in excel! Not sure how to use SUMPRODUCT? Watch this video and I'll show you how!
This video is in response to a recent question I received, so if you have a question or subject you would like me to cover, just drop a comment below this video and I will do my best! 😃
Instructor Lead Excel Course ► essentialexceluk.com/products...
VBA Playlist ► essentialexceluk.com/blogs/tu...
More Tutorials ► essentialexceluk.com/blogs/tu...
#excel #vba #tutorial #msexcel #macros #visualbasic
I hope you enjoyed this tutorial 😃 If you did please give it a LIKE... and don’t forget to SUBSCRIBE for even more content!
For More 👉 ruclips.net/user/EssentialExcel
How about for January? Blanks are also counted as 1
Thank u for the video. Great one 👌
Incredibaly helpful. Thanks a lit.
Thank you! Glad it helped 🙂
THANK YOU SO MUCH!!!
For December is counting all the occurrences of 12 but all so the empty cells
hI! when I put one (1) for January, it's not working :( help
Worked well apart from January, any advice?
I’m having an issue with January also
I am also having an issue with January IF I have blanks in the list.
thanks very helpful
Thank you George!
Great video. I do have one question though. I am currently counting the number of times a date comes up in a dataset. However, the dataset has got “TBC” next to some actions, so not every cell is populated with a date. This then messes up the formula and instead of giving an answer just provides “#Value!”. Could you recommend a way to fix this issue. Thanks!
❤❤❤🎉🎉🎉 thanks sir.
Exactly what I was looking for. Thanks much!
blank cells is counted, what formula for exception?
Really good and a big help. Similarly, my range covers more than one year eg I might have 3 Februarys in 2022 and 1 in 2023. How do I show how many in each year because this way will show 4 overall?
i also want to about this how do i know which formula to use for this situation?
I’m doing this but changing the month value to an entire column (g:g) and getting a value error. Would you know why this is the case? Thanks for the video!
What would the formula be if I wanted to count the number of occurrences of a person's name in a month?
I have used this formula with success until I needed to count the times December appeared. The formula then returned the wrong count, I think this is because there are clank cells and all blank cells equal December... so how can I ignore the blank cells within this formula? =SUMPRODUCT(--((MONTH(L2:L472)=12)))
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. 🙂
Hello 👋. This sounds like a topic others would benefit from in a video, will see if I can get this produced over the weekend. 🙂
How would I count the number of occurrences in a month based on a name in another column? Thanks
My range of approx 4,000 cells includes blank cells; as the range gets completed as the year progresses.
Currently there's data for Apr to Jun, the rest show 0 except Jan; which shows approx 3,200 as the blank cells in my range returns as a 1, which is Jan.
Can you help with this please to ignore the blank cells.
Many thanks.
Did you ever find a solution to this
@Matthew Aligo I'm almost certain I did as I recall the totals all calculating correctly and being able to submit accurate reports. However, since then, the business I work for no longer requires that data to be collected so the workbook has been archived.
@@braddas24 please share how you ignored blanks. I also need the way. Thank you in advance.
Never mind @braddas24, I got it, it's =SUMPRODUCT(--(IF(ISBLANK($ER$3:$ER$113),0,(MONTH($ER$3:$ER$113))=1)))
@@jrock-hk7rm I have found the archived file.
I had my data in a worksheet named ‘Data’ and the date in this data is in column F. The totals per month are reported in a separate worksheet in column O, and in this worksheet, I manually listed the month in column M, the year in column N and the totals in column O.
The formula I used in Column O was from many Google searches and a bit of assistance from my colleague. The following formula is for April 2022.
=IF(SUMPRODUCT((MONTH(Data!$F$2:$F$3756)=4)*(YEAR(Data!$F$2:$F$3756)=N3))=0,"",SUMPRODUCT((MONTH(Data!$F$2:$F$3756)=4)*(YEAR(Data!$F$2:$F$3756)=N3)))
To be totally honest, I don’t recall exactly how it works … but it does so was happy with that.
Hope it helps you.
its not working for me