IF Formula comparing SUM values

Mike Buckiewicz

I am struggling too figure out how I can make this formula work:

=IF((SUM([Billable PM Hours]@row, [Billable Consultant/Developer Hours]@row)) > (SUM([Consultant/Developer SOW Hours]@row, [SOW PM Hours]@row, 1)

Intent is to have these Sums compare to each other so that when Billable Hours Sum is over the SOW Hours Sum, the At risk Flag is Checked. The last time I ran this, I only got 6 results all random and no indicators why. I have about 20 lines that meet this condition.

Any guidance would be appreciated.

Mike Buckiewicz

@Paul Newcome @Genevieve P. what's funny is I did my own sleuthing and discovered for some reason the numbers were coming in as text. We have an integration we are testing that pulled in the numbers from our accounting system and saw that the code was bringing in the hours with an apostrophe at the beginning thus indicating text. As soon as I tested entering the number in manually as a number, everything worked as previously expected with the formulas.

Thanks or confirming m insanity! 😀

Mike Buckiewicz

I also tried to simplify to see if I could do a basic formula and this one is pulling up rows that do not meet this criteria. Yet it acts correctly on other rows. Not sure what is going on:

=IF([Billable Consultant/Developer Hours]@row > [Consultant/Developer SOW Hours]@row, 1)

Some rows it reacts accordingly while others it tags as over budget but the formula is not true from above.

Genevieve P.

Hi @Mike Buckiewicz

It looks like you just have a parentheses out of place. Essentially we just needed to close off the second SUM statement before telling the IF statement what to do:

=IF( SUM(this) > SUM(that), 1, 0)

Try this:

=IF(SUM([Billable PM Hours]@row, [Billable Consultant/Developer Hours]@row) > SUM([Consultant/Developer SOW Hours]@row, [SOW PM Hours]@row), 1, 0)

If this doesn't work, is it possible that your numbers are being seen as text in the cell? You can identify this based on how the numbers appear in your columns. Numbers seen as text will appear on the left of the cell; numbers seen as numerical values will appear on the right side of the cell.

If any of your columns have the numbers appearing on the left, how is the number being created? Are you using another formula to generate this number? We can use the VALUE function to translate text-numbers into values. It would be helpful to know your other formulas in this case and see a screen capture of your sheet (but please block out any sensitive data).

Cheers,

Genevieve

Paul Newcome

How exactly are your columns populated? It almost seems as if some of the rows have the numbers stored as text values.

Mike Buckiewicz

@Paul Newcome @Genevieve P. what's funny is I did my own sleuthing and discovered for some reason the numbers were coming in as text. We have an integration we are testing that pulled in the numbers from our accounting system and saw that the code was bringing in the hours with an apostrophe at the beginning thus indicating text. As soon as I tested entering the number in manually as a number, everything worked as previously expected with the formulas.

Thanks or confirming m insanity! 😀

Paul Newcome

Glad you got it sorted. 👍️