SUMIFS I think?

I am trying to count a total number of employees in a multi-select drop down and also only count those employees if they attended meetings in November, December, January, Etc. All of this data is absorbed via a form.

My current formula is:

=SUMIFS([Employees at meeting]:[Employees at meeting], >1, Date:Date, AND(@cell >= DATE(2021, 11, 1), @cell <= DATE(2021, 11, 31)))

If I run a COUNTIFS, my returned amount is "1", showing their is a submission, but not counting the number of selections in the multi-select cell:

=COUNTIFS([Employees at meeting]:[Employees at meeting], >1, Date:Date, AND(@cell >= DATE(2021, 11, 1), @cell <= DATE(2021, 11, 31)))

I am trying to produce this in a sheet summary on the right hand side.

What am I missing here?

Kyle

Find more posts tagged with

Formulas

Dashboards

Accepted answers

Paul Newcome

Yeah. You are going to want a COUNTM/COLLECT combo.

=COUNTM(COLLECT([Employees At Meeting]:[Employees At Meeting], Date:Date, AND(IFERROR(MONTH(@cell), 0) = 11, IFERROR(YEAR(@cell), 0) = 2021))

All comments

Paul Newcome

Are you able to provide a screenshot for reference? It almost sounds like what you are going to need is a COUNTM/COLLECT combo.

Kyle Hicks

Both categories I am looking to quantify are shown. Employees at meeting is the multi select, Date is meant to be used for a monthly count of employees total, the number of meetings is not as important.

Paul Newcome

Yeah. You are going to want a COUNTM/COLLECT combo.

=COUNTM(COLLECT([Employees At Meeting]:[Employees At Meeting], Date:Date, AND(IFERROR(MONTH(@cell), 0) = 11, IFERROR(YEAR(@cell), 0) = 2021))

Kyle Hicks

Paul,

Woah, I need to get on your level.

Great work man!!

Paul Newcome

Happy to help. 👍️

You can think of the COLLECT function as adding an "IFS" to any other function. Want to join certain cells but only if they meet certain criteria? We don't have a JOINIFS, but we do have JOIN/COLLECT. Same for here. You wanted to count the number of entries in a series of multi-select cells (COUNTM) but only if they met certain criteria (COLLECT).

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.