Improper Value with my Code!
Hello!
I'm needing some help with my Smartsheet's formula. For whatever reason I do not know I get a totally wrong number although I believe it is correct. Any help would be appreciated!
=SUMIFS({GDP Project Tracker Range 1}, {GDP Project Tracker Range 5}, "<0.05", {GDP Project Tracker Range 3}, "Active") + SUMIFS({GDP Project Tracker Range 1}, {GDP Project Tracker Range 5}, "<=0.05", {GDP Project Tracker Range 3}, "Launched") + SUMIFS({GDP Project Tracker Range 1}, {GDP Project Tracker Range 5}, "<=0.05", {GDP Project Tracker Range 3}, "Planned")
I want the sheet to look for everything less then 5% in one column and then display results if it is active, launched, and planned. If I change that first range statement from 1 to something else i get even crazier values. Please help!
Answers
-
I don't see any issues with this formula. Is it possible some of the values in {GDP Project Tracker Range 1} are formatted as text?
-
How do you know what each separate range is. I think that is my problem, but I do not understand it at all. Throughout the spreadsheet I am using there are number values and text.
Thanks!
-
Try this:
=SUMIFS({GDP Project Tracker Range 1}, {GDP Project Tracker Range 5}, @cell < 0.05, {GDP Project Tracker Range 3}, OR(@cell = "Active", @cell = "Launched", @cell = "Planned"))
-
Thanks for the comment. I get the same incorrect value somehow. I should be getting 33 all summed up where I am getting 109 somehow. I dont understand and I know it is hard without the sheet infront of you
-
Your original formula and the one posted by @Leibel S should give the same result. If you plug the following formula in, it will tell you how many cells in your range are formatted as text. If you are expecting integers, you would expect the result to be zero.
=COUNTIF({GDP Project Tracker Range 1}, ISTEXT(@cell))
-
@Carson Penticuff I don't think it is an issue of text vs numbers in the first range. If there was text when it should be numbers then the result would be lower than expected, but the result is coming out higher than expected.
@EMueller Exactly what steps are you taking to determine that it should be 33? Are you applying a filter to the sheet?
-
Applying a fiter onto the sheet of each thing I am looking for:
The three values selected are the "Active, Launched, Planned". With doing so I have 33 rows of results which fall under this category.
-
By plugging in the formula, you sent above I get 5 as a result.
-
Are you trying to count how many rows or sum a column?
-
I agree with @Leibel S. In your last post you make it sound like you are wanting to count rows and not sum the values in another column. Just counting the rows would use a COUNTIFS like so:
=COUNTIFS({GDP Project Tracker Range 5}, @cell < 0.05, {GDP Project Tracker Range 3}, OR(@cell = "Active", @cell = "Launched", @cell = "Planned"))
-
@Paul Newcome @Leibel S I was looking to count how many rows fall under the criteria. Part of an automization task I was given. That new code resulted in 48 compared the desired 33 number.
-
It is working!!! Say I wanted to use that same thing but look for a value between 0.02 and 0.05? What would that look like?
I appreciate both of your help!
-
Happy to help. 👍️
You would use something like this:
=COUNTIFS({GDP Project Tracker Range 5}, AND(@cell> 0.02, @cell < 0.05), {GDP Project Tracker Range 3}, OR(@cell = "Active", @cell = "Launched", @cell = "Planned"))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!