Count entries for current month

Question

So I want to count how many entries have been filed in the month of July. Could someone help me how to do that so every month when the new data is added it automatically updates and gives new data for each month. I know I need to use combination of =CountIfs, =today, Month, =Year but not sure how exactly to formulate that.

You should show us your data if possible. – Tim Biegeleisen Jul 05 '16 at 17:03 — Tim Biegeleisen, Jul 05 '16 at 17:03

score 2 · Accepted Answer · answered Jul 05 '16 at 17:05

2

Use SUMPRODUCT()

=SUMPRODUCT((YEAR($A$1:$A$100)=YEAR(TODAY()))*(MONTH($A$1:$A$100)=MONTH(TODAY())))

This will count every month that is the same as the month today.

answered Jul 05 '16 at 17:05

Scott Craner

148,073
10
49
81

Thanks for the answer! However when I put that formula it come up as "#Value". I wanna know how many entries have been made in Column "G". So I copied the formula and changed A to G but it won't work. – Nika Dolidze Jul 05 '16 at 17:22
Make sure your dates are true dates and not text that look like dates. Change the column to "General" format. True dates will change to numbers around 42500. Text dates will stay the same. Also make sure you are not including a title cell in G1. If your dates start in G2 then start there. If this encounters any text it will return #Value. @NikaDolidze – Scott Craner Jul 05 '16 at 17:31
So it seems to be working now. However the problem is that when I include cell A and i manually input stuff it works, but it is not working for cell G so I am guessing there is a formatting issue. Can you please explain the formatting part? I don't get what do I have to set that as? I have set as "Short Date" – Nika Dolidze Jul 05 '16 at 18:03
Change it to "General" and tell me what you see. @NikaDolidze – Scott Craner Jul 05 '16 at 18:06
I select the whole column and when I change to general I see numbers around 42,500. – Nika Dolidze Jul 05 '16 at 18:15
Do you see any cells that the formula reference that are text or errors? @NikaDolidze – Scott Craner Jul 05 '16 at 18:19
Nope, it simply says "0". and even when I manually add data for ex: 7/22/16 it still doesn't change from "0" to "1". – Nika Dolidze Jul 05 '16 at 18:36
NVM, it works now :) Thank you ! Wit that being said, would yopu know how to now transform this formula into quarters? so same thing except now I would like to count all the entries from 1/1/16 - 4/1/16 – Nika Dolidze Jul 05 '16 at 18:38
New Questions should be asked in a separate post. Please mark this one as correct by clicking the check mark by the answer and ask your new question. First try using COUNTIFS() with your date extents. – Scott Craner Jul 05 '16 at 18:41
I just opened a new topic: would you please be able to help me there? http://stackoverflow.com/questions/38211424/data-up-to-certain-point – Nika Dolidze Jul 05 '16 at 20:24

Tom Sharpe · Answer 2 · 2016-07-05T19:33:53.207

Here is the COUNTIFS version for the original question

=COUNTIFS($A$1:$A$100,"<="&EOMONTH(TODAY(),0),$A$1:$A$100,">"&EOMONTH(TODAY(),-1))

or

=COUNTIFS($A$1:$A$100,"<"&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),$A$1:$A$100,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

You might be thinking of something like =countifs(month($A$1:$A$100),month(today)) but unfortunately it doesn't work - the first value in the countifs has to be a range and you can't use a function here so it has to be done with a SUMPRODUCT as in Scott Craner's answer.

Count entries for current month

2 Answers2