# Improper Value with my Code!

Options
✭✭

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!

• ✭✭✭✭✭✭
Options

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?

• ✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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"))

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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))

• ✭✭✭✭✭✭
Options

@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?

• ✭✭
Options

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.

• ✭✭
Options

By plugging in the formula, you sent above I get 5 as a result.

• ✭✭✭✭✭✭
Options

Are you trying to count how many rows or sum a column?

• ✭✭✭✭✭✭
Options

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"))

• ✭✭
Options

@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.

• ✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!