CountIfs Function for Daily Counts

Freddy Bustillos
Freddy Bustillos ✭
inFormulas and Functions

I'm trying to get a count of records on a summary sheet, referencing another sheet that match multiple criteria by date, for multiple days. For example: I'd like get a count for all matching criteria for 03/18/2020, and then a separate count for all matching criteria for 03/19/2020, etc.

This is formula I'm using, but I just need to add the portion that will CountIfs by individual date:

=COUNTIFS({EMPLOYEE AGENCY}, OR(@cell = "Fire", @cell = "Lifeguard", @cell = "Non-Sworn"), {EMPLOYEE RISK LEVEL}, "Low")

Any idea how to accomplish this?

· Share on Facebook Share on Twitter

Best Answer

Answers

  • Freddy Bustillos
    Freddy Bustillos ✭

    REALLY GOOD! That worked like a charm. Thank you so, so much!

    · Share on Facebook Share on Twitter
  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Happy to help.

    Kind regards

    Debbie

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
You are close, your syntax is just off by a bit.<\/p>

=IF(OR(Name@row = \"AB\", Name@row = \"CD\"), [Number1]@row, SUM([Number2]@row:[Number5]@row))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108545,"type":"question","name":"If, Index & Collect Formula issues","excerpt":"I have an issue with my current If, Index, and Collect formula. I have two sheets. One for data collection, and another for the Reviewer's Agency, Schedule, and Email address. I want to automate this process where if I have a TFD C shift, the Reviewers email address will be added into the data collection sheet. Here's my…","snippet":"I have an issue with my current If, Index, and Collect formula. I have two sheets. One for data collection, and another for the Reviewer's Agency, Schedule, and Email address. I…","categoryID":322,"dateInserted":"2023-08-04T16:47:48+00:00","dateUpdated":null,"dateLastComment":"2023-08-04T21:01:33+00:00","insertUserID":134622,"insertUser":{"userID":134622,"name":"Office of the Medical Director","url":"https:\/\/community.smartsheet.com\/profile\/Office%20of%20the%20Medical%20Director","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-04T21:09:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":134622,"lastUser":{"userID":134622,"name":"Office of the Medical Director","url":"https:\/\/community.smartsheet.com\/profile\/Office%20of%20the%20Medical%20Director","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-04T21:09:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":47,"score":null,"hot":3382353561,"url":"https:\/\/community.smartsheet.com\/discussion\/108545\/if-index-collect-formula-issues","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108545\/if-index-collect-formula-issues","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108545,"commentID":389049,"name":"Re: If, Index & Collect Formula issues","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389049#Comment_389049","dateInserted":"2023-08-04T21:01:33+00:00","insertUserID":134622,"insertUser":{"userID":134622,"name":"Office of the Medical Director","url":"https:\/\/community.smartsheet.com\/profile\/Office%20of%20the%20Medical%20Director","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-04T21:09:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/K7YSB4ZJ9B09\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-04T21:01:11+00:00","dateAnswered":"2023-08-04T19:31:13+00:00","acceptedAnswers":[{"commentID":389035,"body":"

Try an INDEX\/COLLECT instead.<\/p>

=INDEX(COLLECT({Contact Column}, {Agency Column}, @cell = Agency@row, {Schedule Column}, @cell = Schedule@row), 1)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108548,"type":"question","name":"Track hours for a specific task in a specific month","excerpt":"Hi everyone, So I am trying to track the hours I spend doing task called e.g. \"Test 1\" in a given month. I have a separate spreadsheet with Dates (Month, day, year), Duration (in Hours) and column for Category (where I have \"Test 1\"). Could you help me with a formula to track this? Thank you!","snippet":"Hi everyone, So I am trying to track the hours I spend doing task called e.g. \"Test 1\" in a given month. I have a separate spreadsheet with Dates (Month, day, year), Duration (in…","categoryID":322,"dateInserted":"2023-08-04T17:11:38+00:00","dateUpdated":null,"dateLastComment":"2023-08-04T20:14:33+00:00","insertUserID":159564,"insertUser":{"userID":159564,"name":"Magda","url":"https:\/\/community.smartsheet.com\/profile\/Magda","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!WVEnHu-2MXs!uiDntzuzmHs!KYtjodCrvWz","dateLastActive":"2023-08-04T20:22:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":159564,"lastUser":{"userID":159564,"name":"Magda","url":"https:\/\/community.smartsheet.com\/profile\/Magda","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!WVEnHu-2MXs!uiDntzuzmHs!KYtjodCrvWz","dateLastActive":"2023-08-04T20:22:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3382350371,"url":"https:\/\/community.smartsheet.com\/discussion\/108548\/track-hours-for-a-specific-task-in-a-specific-month","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108548\/track-hours-for-a-specific-task-in-a-specific-month","format":"Rich","lastPost":{"discussionID":108548,"commentID":389043,"name":"Re: Track hours for a specific task in a specific month","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389043#Comment_389043","dateInserted":"2023-08-04T20:14:33+00:00","insertUserID":159564,"insertUser":{"userID":159564,"name":"Magda","url":"https:\/\/community.smartsheet.com\/profile\/Magda","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!WVEnHu-2MXs!uiDntzuzmHs!KYtjodCrvWz","dateLastActive":"2023-08-04T20:22:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/41LONIWI0UD8\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-04T20:14:06+00:00","dateAnswered":"2023-08-04T18:53:19+00:00","acceptedAnswers":[{"commentID":389017,"body":"

You would use a SUMIFS.<\/p>

=SUMIFS({Hours}, {Category}, @cell = \"Test 1\", {Dates}, AND(IFERROR(MONTH(@cell), 0) = 8, IFERROR(YEAR(@cell), 0) = 2023))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • If OR and SUM formula

    Hi, I created this formula but I'm not sure why I'm getting the error message "INVALID DATA TYPE" =IF(OR(Name@row = "AB", "CD"), [Number1]@row, SUM([Number2]@row:[Number5]@row))

  • If, Index & Collect Formula issues

    I have an issue with my current If, Index, and Collect formula. I have two sheets. One for data collection, and another for the Reviewer's Agency, Schedule, and Email address. I want to automate this process where if I have a TFD C shift, the Reviewers email address will be added into the data collection sheet. Here's my…

  • Track hours for a specific task in a specific month

    Hi everyone, So I am trying to track the hours I spend doing task called e.g. "Test 1" in a given month. I have a separate spreadsheet with Dates (Month, day, year), Duration (in Hours) and column for Category (where I have "Test 1"). Could you help me with a formula to track this? Thank you!