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

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.

Find more posts tagged with

Accepted answers

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

All comments

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

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! 🙂

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.