How to COUNTIFS with multiple criteria

I am trying to create a COUNTIF formula with multiple criteria. I have created a functioning formula for each condition but it stops working when I combine them together. In the example pictured, I would like to count the number of occurrences for each proctor when the duration is greater than or equal to 05:00 (and then conversely when the duration is less than 05:00). The calculations are happening on a second grid, where the calculation is based on the value in the Proctor Name column.

Searching in the Community helped me to create each half of the formula (and SheetName PROCTORS has been set as the range to include both proctor columns):

to count number of occurrences for each proctor: =COUNTIF({SheetName PROCTORS}, HAS(@cell, [PROCTOR NAME]1))

to count number of occurrences for Duration greater than or equal to 05:00: =countif({SheetName DURATION}, >="05:00")

I've tried combining these two conditions with a COUNTIFS but it keeps coming up with an error: =COUNTIFS({SheetName PROCTORS}, HAS(@cell, [PROCTOR NAME]1), {SheetName DURATION}, >="05:00").

Any help would be much appreciated!

Find more posts tagged with

Accepted answers

James Keuning

I believe the 5:30 is causing the problem. Particularly the quotes, which (in my mind, I do not actually know what Smartsheet does with that) means the number/time is being treated as text, so there is not greater than or less than with the text. The fact that the values are aligned left in the cell also indicates text. You should convert your durations to decimals. Change some of the values to 3.75 and 5.5 and then change the formula to just >=5 and you will see that it works.

All comments

James Keuning

Mandy and Jen

@James Keuning, thank you for your suggestions! I converted the values in the Duration column to numbers with decimals, as pictured below. The two conditions still work in their individual COUNTIF formulas, however, it still breaks when I try to string them together with the COUNTIFS and receive an Unparseable or Incorrect Argument Set error. This is my formula to put the two conditions together:

=COUNTIFS({SheetName PROCTORS}, HAS(@cell, [PROCTOR NAME]1), {SheetName DURATION}, >=5)

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.