Formula for meeting two criteria and a certain date

Steve S · 04/05/19

I have a Date Column formatted as such [01/01/19 3:59 PM]

I have a "Request Type" column which has 3 different request types. (SCHCOM, GSA and OFC MGMT)

I need a formula to show how many requests by type by month.

How many of each request type came into the sheet in Jan, Feb, Mar. etc.....

I tried a few different versions of a formula but was not successful.

Anything you can suggest to get me going in the right direction would be appreciated.

Nic Larsen · 04/05/19

This should give you the format to use to count for January. Update the = 1 to 2, 3, and so on for each month you want to account for:

=COUNTIFS([Request Type]:[Request Type], "SCHCOM", [Date1]:[Date1], IFERROR(MONTH(@cell), 0) = 1)

=COUNTIFS([Request Type]:[Request Type], "GSA", [Date1]:[Date1], IFERROR(MONTH(@cell), 0) = 1)

=COUNTIFS([Request Type]:[Request Type], "OFC MGMT", [Date1]:[Date1], IFERROR(MONTH(@cell), 0) = 1)

You'll also need to update the ranges for your own.

Steve S · 04/09/19

I have tried (what I believe) is every possible combination and I cant get it to work. Thanks for trying. I will go back to the formula board search tomorrow.

Nic Larsen · 04/09/19

Do you want to share a screenshot of your sheet or make a copy of it and share it? Maybe i can troubleshoot it.

Andrée Starå · 04/10/19

Hi Steve,

To add to Nic's excellent formula.

I'd recommend adding a MonthNumber Helper column where you can add 1-12 and then you can reference it in the formula instead of manually updating the number for all the metrics that you need.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Formula for meeting two criteria and a certain date

Comments

Categories