Formula to count how many applications received per month per agency over years

Options

I have two columns, 'received by date' (date column) and the type of 'agency' it was received from which are two columns I need to formulate a number for.

I need to count how many applications were received for that month per agency. There are 16 agencies that can be selected under the agency column that can be grouped as "MO" and 2 agencies I want to group as "DPC/OP".
I have tried this but no luck: =COUNTIFS({NEW - Received Date}, IFERROR(MONTH(@cell), 0) = 6), IFERROR(YEAR(@cell), 0) = 2024)

I have attached the sheet I would like the data to populate into. The other sheet is being used as the reference sheet - columns are 'AGENCY' and 'NEW - Received Date'. Thank you!

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @shanriddell,

    So do you want a formula for the "MO" and "DPC/OP" columns, or are you looking for the totals? For totals, you should be able to use something like this:

    =COUNTIFS(Date:Date, IFERROR(MONTH(@cell), 0) = 6, Date:Date, IFERROR(YEAR(@cell), 0) = 2024)

    It looks like you forgot to include the second range for the YEAR check in your original formula

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • shanriddell
    Options

    Thanks so much for your assistance!! That definitely helps me for the total amount (number 3 below). I am hoping I can explain the other 2 below with some sense haha! 😄

    3 separate totals I am after:

    1. How many applications received in a month that are selected as either Whitby, Winton, Michael etc (for 16 different agencies) via a drop down list via an other reference sheet. These are added together to produce the total under the MO column.
    2. How many applications received in a month that are selected as either DPC or OP via a drop down list via an other reference sheet. These are added together to produce the total under the DPC/OP column.
    3. The total of the above added together which you have assisted me with above - thank you!
      =COUNTIFS({Received Date}, IFERROR(MONTH(@cell), 0) = 6, {Received Date}, IFERROR(YEAR(@cell), 0) = 2024)

      Thank you sooo much! Let me know if you need me to provide any screenshots of the reference sheet

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!