Countifs year and month of one cell match year and month of other cell and several other criteria

[rfigueroa1976]

Trying to get a count of orders that were shipped out of a city during a year and month listed in another cell...

Countifs city in column B:B is the same as f2, state in column C:C is the same as g2, Month in Column d:d is the same as the month in the date in f3, Year in Column d:d is the same as the month in the date in f3.

The formula i currently have is =COUNTIFS(B:B,F2,C:C, G2) but still need to add the month and year portion of the formula.

I'd attach a worksheet but I currently don't have permission to do so.

 

[Tetra201]

Try

=COUNTIFS(B:B,F2,C:C,G2,D:D,">="&EOMONTH(F3,-1)+1,D:D,"<"&EOMONTH(F3,0)+1)

 

[rfigueroa1976]

Not coming back with correct counts Also, can you please explain how the eomonth function is working. Does that take into consideration the year as well?

 

[Tetra201]

The formula has been tested to work with a mock-up dataset.

The EOMONTH function works as follows:

If you have, say, 16-Jan-2019 in cell F3,
EOMONTH(F3,-1) returns 31-Dec-2018
EOMONTH(F3,-1)+1 returns 1-Jan-2019
EOMONTH(F3,0) returns 31-Jan-2019
EOMONTH(F3,0)+1 returns 1-Feb-2019

So, it does take into consideration the year as well.