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

Find more posts tagged with

Accepted answers

SHOOD

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

All comments

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

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

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!

Paul Newcome

Happy to help. 👍️

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

https://community.smartsheet.com/discussion/comment/312946#Comment_312946

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

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.