CODEWITHSUNDEEP
excelcountif
Nika Dolidze
Nika Dolidze

Reputation: 33

Count entries for current month

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.

Upvotes: 0

Views: 1033

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

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.

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152450

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.

Upvotes: 2

Related Questions