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

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!

Find more posts tagged with

Accepted answers

All comments

bisaacs

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!

shanriddell

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:

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.
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.
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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.