Using COUNTIFS with different date formats

Question

I am trying to calculate the totals of a column when the date column is within a certain month. The value to find is in a different date format to the format in the criteria range and I can't figure out how create a formula using the two different date formats. Here are the columns where I am trying to get the totals from:

Here is where I am trying to count the rows when it falls within a certain month and year:

This is the formula I have created but this doesn't work and just returns 0:

=COUNTIFS($A$3:$A$27, YEAR(G3), $A$3:$A$27, MONTH(G3))

I have also tried this but it is still returning 0:

=COUNTIFS($A$3:$A$27, "=YEAR(G3)", $A$3:$A$27, "=MONTH(G3)")

You could use an array, so having top table in A1:A10 and bottom in C1:C12, you could have the following =sum(--(year($A$1:$A$10)=(year(C1))*(month($A$1:$A$10)=month(C1))) This is an array formula so press CTRL + SHIFT + ENTER. — Nathan_Sav
– Nathan_Sav, Commented Apr 21, 2021 at 10:15
Assuming G3 is actually 1 Jan 21 formatted to display mmm-yy, you could use: =COUNTIFS($A$3:$A$27, ">="&G3, $A$3:$A$27, "<="&EOMONTH(G3,0)) — Rory
– Rory, Commented Apr 21, 2021 at 10:28

Ron Rosenfeld · Accepted Answer · 2021-04-21 10:31:00Z

3

Format is irrelevant if your dates are REAL dates

If your dates are "real" dates, merely formatted a certain way
And if your Months are also real dates where the date is actually the first of the month,

Then:

=COUNTIFS(Dates,">="&B15,Dates,"<=" &EOMONTH(B15,0))

Where Dates is the Named Range containing your list of dates, and B15 contains your month/year formatted first of the month date.

If your months column is not the first of the month, then a minor change is all that is needed to convert the first criteria to the first of the month:

=COUNTIFS(Dates,">="&DATE(YEAR(B15),MONTH(B15),1),Dates,"<=" &EOMONTH(B15,0))

answered Apr 21, 2021 at 10:31

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bail P Over a year ago

Thanks for this. What does the & represent when using ">="&B15?

Ron Rosenfeld Over a year ago

@BailP concatenation operator

Collectives™ on Stack Overflow

Using COUNTIFS with different date formats

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related