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
-
Thanks Paul! I had just turned my focus on Sumifs. Working like a charm!
Answers
-
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.
-
Thanks Paul! I had just turned my focus on Sumifs. Working like a charm!
-
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!
-
Happy to help. 👍️
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!