Countif formula help

Hello,

I have a formula in my Sheet Summary that is working fine:

=COUNTIFS([ID Type]:[ID Type], TestType#, [In Scope]:[In Scope], 1, Workset:Workset, Workset#, Status:Status, "Ready for Test")

I need to be able to count some rows in the Smartsheet as more than 1 in some cases.

I added a column called Data Combination Count.

If I need the row to count multiple times, the number of times will be entered in the Data Combination Count column.

I then need to change the formula so it is counting the row correctly whether there is a value in the Data Combination Count column or not.

I appreciate any help you can provide.

Thanks!

Susan

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If you include a portion in the Data Combination formula, you could output the number of times you want to count the entry to include even if it is 1 time. From there you could run a SUMIFS on just the Data Combination Count column.

  • SHOOD
    SHOOD ✭✭✭✭
    Answer ✓

    Thanks Paul! I had just turned my focus on Sumifs. Working like a charm!

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭

    Hey Susan - what's the maximum amount of times you'd need to count each row? If it's not many, I'd just do something like this for your formula (copy/paste the whole thing):

    -----

    =COUNTIFS([ID Type]:[ID Type], TestType#, [In Scope]:[In Scope], 1, Workset:Workset, Workset#, Status:Status, "Ready for Test", [Data Combination Count]:[Data Combination Count],"")+

    (COUNTIFS([ID Type]:[ID Type], TestType#, [In Scope]:[In Scope], 1, Workset:Workset, Workset#, Status:Status, "Ready for Test", [Data Combination Count]:[Data Combination Count],2)*2)+

    (COUNTIFS([ID Type]:[ID Type], TestType#, [In Scope]:[In Scope], 1, Workset:Workset, Workset#, Status:Status, "Ready for Test", [Data Combination Count]:[Data Combination Count],3)*3)+

    (COUNTIFS([ID Type]:[ID Type], TestType#, [In Scope]:[In Scope], 1, Workset:Workset, Workset#, Status:Status, "Ready for Test", [Data Combination Count]:[Data Combination Count],4)*4)+

    (COUNTIFS([ID Type]:[ID Type], TestType#, [In Scope]:[In Scope], 1, Workset:Workset, Workset#, Status:Status, "Ready for Test", [Data Combination Count]:[Data Combination Count],5)*5)

    -----

    And I think that'd likely work. At least, it does in my head. This is a tough one!

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

  • SHOOD
    SHOOD ✭✭✭✭

    This is what I ended up with that is working:

    =SUMIFS([Data Combination Count]:[Data Combination Count], [ID Type]:[ID Type], TestType#, [In Scope]:[In Scope], 1, Workset:Workset, Workset#, Status:Status, "Not Started")

    Super easy once I got my brain off of the countifs formula. I appreciate everyone's help!

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭

    That's a much more condensed version of the formula I came up with. 😁 Well done!

    If this answer answers your question, please press "Yes" above - it helps the community (and those random Googlers out there 👀) find solutions like yours faster.

    Love,

    Brett Wyrick | Connect with me on LinkedIn.

    ------------------------------------------------------------------------------

    2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!