Question description:
This user has given permission to use the problem statement for this
blog.
I am trying to find a formula to count the number of times a month and year occurs
Solved by G. H. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
15/09/2017 - 03:01
Welcome to Excel Got-it pro
Excelchat Expert
15/09/2017 - 03:01
How may I help you today?
User
15/09/2017 - 03:01
I am trying to find a fuction to count the number of times confirm times appear in Sepetermber 2017 and August 2017
User
15/09/2017 - 03:01
using pivot table
User
15/09/2017 - 03:01
or an excel formula
Excelchat Expert
15/09/2017 - 03:01
sorry I didn't get you.
Excelchat Expert
15/09/2017 - 03:02
Can you please be more specific like what is confirm time.
User
15/09/2017 - 03:02
No worries
User
15/09/2017 - 03:02
Did you see what I add in column A
User
15/09/2017 - 03:02
Do you see how in each row, there is a date
User
15/09/2017 - 03:02
I am trying to count the number of times September 2017 pops up
User
15/09/2017 - 03:02
Without manually counting it
Excelchat Expert
15/09/2017 - 03:03
okay now I got it.
Excelchat Expert
15/09/2017 - 03:03
you want to count how many times a month have occurred right?
Excelchat Expert
15/09/2017 - 03:06
is it?
User
15/09/2017 - 03:08
hi
User
15/09/2017 - 03:08
si sirry i just got ths
User
15/09/2017 - 03:08
I am trying to do the month of the year
User
15/09/2017 - 03:08
so how many counts in september 2017
Excelchat Expert
15/09/2017 - 03:09
okay
User
15/09/2017 - 03:11
would a pivot table be the best thing to do?
Excelchat Expert
15/09/2017 - 03:11
nope, I formula
User
15/09/2017 - 03:12
So there are data for January in 2017 and 2016
User
15/09/2017 - 03:12
can you differentiate them
Excelchat Expert
15/09/2017 - 03:12
yes, gimme some time
Excelchat Expert
15/09/2017 - 03:15
so your data is in excel?
User
15/09/2017 - 03:16
yes
Excelchat Expert
15/09/2017 - 03:19
Here you go.
Excelchat Expert
15/09/2017 - 03:19
Please see the results
User
15/09/2017 - 03:19
i counted the sepetmber 2017 and i got 1718
Excelchat Expert
15/09/2017 - 03:20
Well this is my formula
Excelchat Expert
15/09/2017 - 03:20
=SUMPRODUCT(1*(MONTH($A$2:$A$30000)=MONTH(C9)))
User
15/09/2017 - 03:20
there are data for 2016 too
User
15/09/2017 - 03:20
maybe you combined the two years
User
15/09/2017 - 03:20
is there someone else that can help us
Excelchat Expert
15/09/2017 - 03:21
no need, I'm enough.
Excelchat Expert
15/09/2017 - 03:21
So, now I'm gonna modify the formula to exclude dates from 2016,15
User
15/09/2017 - 03:22
thx
Excelchat Expert
15/09/2017 - 03:22
or you want to count months from those years too?
User
15/09/2017 - 03:22
i do for all of the months/year
User
15/09/2017 - 03:24
I got it
User
15/09/2017 - 03:24
I used pivot table
User
15/09/2017 - 03:24
If you can find the formula, that would be great too
User
15/09/2017 - 03:24
I can wait
Excelchat Expert
15/09/2017 - 03:24
okay
Excelchat Expert
15/09/2017 - 03:32
now see the result please
User
15/09/2017 - 03:33
yes that is what i got thanks
Excelchat Expert
15/09/2017 - 03:33
Thanks for the patience
User
15/09/2017 - 03:33
thank you for helping me
Excelchat Expert
15/09/2017 - 03:33
My pleasure
Excelchat Expert
15/09/2017 - 03:33
Have a great day
User
15/09/2017 - 03:34
wait
User
15/09/2017 - 03:34
oh nvm
User
15/09/2017 - 03:35
hm its not working for me
Excelchat Expert
15/09/2017 - 03:36
what is the problem?
User
15/09/2017 - 03:36
the count gives me 0
Excelchat Expert
15/09/2017 - 03:36
for which month?
User
15/09/2017 - 03:36
for all of them
Excelchat Expert
15/09/2017 - 03:36
=SUMPRODUCT(1*(MONTH($A$2:$A$30000)=MONTH(D2))*(YEAR($A$2:$A$30000)=$C$2))
Excelchat Expert
15/09/2017 - 03:37
apply this for 2017
Excelchat Expert
15/09/2017 - 03:37
as you can see, the result is there
User
15/09/2017 - 03:38
i see, i do not know why it is not working for me
User
15/09/2017 - 03:38
ill figure it out thnks
Excelchat Expert
15/09/2017 - 03:38
where are you applying the formula?
Excelchat Expert
15/09/2017 - 03:40
Okay the session time is up. thanks for choosing excel got it pro.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.