Could a referenced cell formula be messing up my SumIf Formula?

Kirby A

I have two sheets. Sheet A with a list of Projects, Project ID, and Amount of Funds awarded. Sheet B contains order information. The information is taken through a FormStack Form, but on this form, the project owner selects their project name that is basically a list of their project combined with the project ID. Once that information is brought into Sheet B, a different column splits up that order information and so I am left with the Project ID # in its own cell (=RIGHT([Project Assigned]@row, LEN([Project Assigned]@row) - FIND(":", [Project Assigned]@row))).

After all of this, I want a SumIf formula for the total amount spent on Sheet B / Order Form. It would look something like this

=SUMIF({Sheet B Project ID #}, [Project ID]@row, {Sheet B Final Cost})

However, I think because the formula is referencing another column formula, it doesn't count it as a match. Is this the case? Is there an easy workaround other than doing something else with the form intake so I don't need a formula to break up the information?

Thank you!

Paul Newcome

It isn't necessarily that it is a formula in general, but the formula's output could cause an issue. Are you able to provide a screenshot for context? How is the [Project ID] entered in Sheet A?

Kirby A

[Project ID] as referred above is [Project #] in practice. My formula would go into the Amount Spent column. Basically, if the Project # matches the Project ID # on Sheet B (Ordering Sheet), then I want it to sum the costs for that matching project. I always seem to get 0.

Paul Newcome

I understand what you are trying to do and what your current result is, but how are you currently entering the [Project #] in Sheet A?

Kirby A

To update, I did make a test sheet with all of the same values and the only difference was that the formula in the screenshot above was taking a range that was also not a formula, whereas this formula in this instance is taking a range that is also a column formula.

So, I think that does answer the question but I do not know an easy workaround other than changing something significantly on the form so that I don't reference a formula in my range.

Paul Newcome

How are you entering the [Project #] in Sheet A?

Kirby A

It is entered by someone else via a Smartsheet Form once that project is awarded.

Paul Newcome

Ok. So it sounds like there may just be an issue with numbers (directly entered) vs text that just looks like numbers (from the RIGHT function).

My suggestion is to insert a column on Sheet A and use this column formula:

=[Project #]@row + ""

That's a plus quote quote at the end there.

Then reference this new column in your formula in Sheet B instead of the original [Project #] column.