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

Kerri S

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.

Kerri S

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!

Genevieve P.

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

Genevieve P.

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

Kerri S

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!

Genevieve P.

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

Kerri S

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

Genevieve P.

No problem at all! 🙂