CODEWITHSUNDEEP
exceldateexcel-formula
DanielleinaD
DanielleinaD

Reputation: 45

MONTH() function, to search through entire column

I am currently trying to learn more about the MONTH function within excel.

Basically, I have a bunch of columns, and I want it to only find those which meet my criteria stated within a countifs function.

At the moment I have.

=COUNTIFS(Sorted!A:A, "HatType", Sorted!D:D, "PantType", Sorted!C:C, MONTH(TODAY()))

It is working for the first two criteria, however id also like to add a check for matching the month of the request.

However, it is returning with 0, despite 3 that should be matching. Month(Today)) is returning 1 (for todays month), I have tried putting the sorted for c column in like this, Month(Sorted!C:C), but it isn't working.

Do you guys have any tips on how to get this working, without creating a new column to separate each dates months.

Upvotes: 1

Views: 834

Answers (1)

user4039065
user4039065

Reputation:

While you can use the MONTH function against an range in a SUMPRODUCT function, you canot use it directly against a range in a COUNTIF or COUNTIFS function. However, you can create a date window for the matching records and COUNTIFS is a vastly more efficient formula.

=COUNTIFS(Sorted!A:A, "HatType", Sorted!D:D, "PantType",
          Sorted!C:C, ">="&DATE(YEAR(TODAY()), MONTH(TODAY()), 1),
          Sorted!C:C, "<"&DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1))

=SUMPRODUCT((Sorted!A:A="HatType")*(Sorted!D:D="PantType")*
            (MONTH(Sorted!C:C)=MONTH(TODAY()))*(YEAR(Sorted!C:C)=YEAR(TODAY())))

Caution: The TODAY function is a volatile¹ function.

¹ Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes. Examples of volatile functions are INDIRECT, OFFSET, TODAY, NOW, RAND and RANDBETWEEN. Some sub-functions of the CELL and INFO worksheet functions will make them volatile as well.

Upvotes: 1

Related Questions