I need SumifS a column when 2 conditions are true on another Smarthsheet, one is a checkbox.

Kerri S
Kerri S ✭✭
in Formulas and Functions

I need to sum the Story Points from a different sheet when the Component column value is 'UI' and the 'Issued Added after Sprint Start' column is checked. My formula is =SUMIFS({Mandalores Sprints Story Points}, {Sprint Name}, Sprint@row, {Mandalore's Sprint Data Issue Added}, "true", {Mandalore's Sprint Data Component}, "UI"). I've tried changing "true" to "1" but it returns the value of '0' when it should be returning '3', as below.

image.png
image.png


· Share on FacebookShare on Twitter

Best Answers

  • Kerri S
    Kerri S ✭✭
    Answer ✓

    Hi G - Oddly enough, this =SUMIFS({Mandalores Sprints Story Points}, {Sprint Name}, Sprint@row, {Mandalore's Sprint Data Issue Added}, 1, {Mandalore's Sprint Data Component}, "UI") returned 0. But, this =SUMIFS({Mandalores Sprints Story Points}, {Sprint Name}, Sprint@row, {Mandalore's Sprint Data Issue Added}, "Yes", {Mandalore's Sprint Data Component}, "UI") returned the correct value of 5.

    Appreciate your support!

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    My apologies! I didn't check the column type in your screen capture - I can see that it's a Text/Number with "Yes" written in the cell, so your second formula is absolutely correct.

    I'm glad you were able to get this working!

    Cheers,

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

    · Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee

    Hi @Kerri S

    Checkboxes can either be set as True or as 1, but in either case there should be no quotes around it, like so:

    {Mandalore's Sprint Data Issue Added}, 1


    Try this:

    =SUMIFS({Mandalores Sprints Story Points}, {Sprint Name}, Sprint@row, {Mandalore's Sprint Data Issue Added}, 1, {Mandalore's Sprint Data Component}, "UI")


    Let me know if that worked for you!

    Cheers,

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

    · Share on FacebookShare on Twitter
  • Kerri S
    Kerri S ✭✭
    Answer ✓

    Hi G - Oddly enough, this =SUMIFS({Mandalores Sprints Story Points}, {Sprint Name}, Sprint@row, {Mandalore's Sprint Data Issue Added}, 1, {Mandalore's Sprint Data Component}, "UI") returned 0. But, this =SUMIFS({Mandalores Sprints Story Points}, {Sprint Name}, Sprint@row, {Mandalore's Sprint Data Issue Added}, "Yes", {Mandalore's Sprint Data Component}, "UI") returned the correct value of 5.

    Appreciate your support!

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    My apologies! I didn't check the column type in your screen capture - I can see that it's a Text/Number with "Yes" written in the cell, so your second formula is absolutely correct.

    I'm glad you were able to get this working!

    Cheers,

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

    · Share on FacebookShare on Twitter
  • Kerri S
    Kerri S ✭✭

    Ahh, now I learned something new too! Thanks so much.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    No problem at all! 🙂

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

    · Share on FacebookShare 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 Formula Handbook template!