COUNTIFS for whole Sheet

Hey,

This is probably an easy fix but I am having no luck.

I want to count if anywhere on a sheet has a "4" in a cell.

I also want this to separate the counts by "ITCMW", "ITCT" & "METC"

My current formula is:

=COUNTIFS([Pre Trip Completed]:[Doors/Gates], HAS(@cell, "4"))

I need to include System:System, "ITCMW" in this formula.

Any thoughts?

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    Can you post some screenshots of your grid layout and sample data?

    Are the below column names?

    I also want this to separate the counts by "ITCMW", "ITCT" & "METC"

    I'm not sure what you're saying here. Can you expand on this?

    I need to include System:System, "ITCMW" in this formula.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Kyle Hicks
    Kyle Hicks ✭✭✭

    Below your first question are not the column names. The column name would be "System" as shown in a screenshot.

    I also attached a screenshot in an attempt to show I am trying to break this total count of "4's" down by the the categories within the System column. Those categories are ITCMW, ITCT and METC. Sorry for the confusion.


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    I think I get it now.

    The COUNTIFS formula allows you to specify that multiple conditions must be met for the row to be counted. However the below will give you an #INCORRECT ARGUMENT SET error...

    =COUNTIFS([Pre Trip Completed]:[Doors/Gates], HAS(@cell, "4"), [System]:[System], "ITCMW")
    

    And breaking the [Pre Trip Completed]:[Doors/Gates] range into individual ranges will only count the rows where ALL the conditions are met, which is not what you want.

    So I'm going to suggest adding a helper column to do some of the heavy lifting on a row by row basis, and then using the summary fields to finish the math.

    Create a helper column "Total4s" that counts the number of 4s on each row. Use this formula (and convert it to a column formula):

    =COUNTIF([Pre Trip Completed]@row:[Doors/Gates]@row, "4")
    

    Then in your Summary fields, use SUMIFS to add up the total number of "4"s where the System field is ITCMW, ITCT, METC, etc.

    =SUMIFS([Total4s]:[Total4s], [System]:[System], "ITCMW")
    

    Repeat for "ITCT" and "METC".

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!