Help with a Sheet Summary Formula Error

Shawn Church

I have a simple sheet summary formula that keeps returning "Invalid Operation". I am wanting to count the number of instances where the Potential Loss column is over $500. I've tried every variation of "500" I can think of (500.00, $500, "500.00"), but it's still returning an error. Any thoughts on what's wrong with my formula? (Bonus if you know a Sheet Summary formula to count specific symbols in a column)

Paul Newcome

I wonder if it has to do with the blanks. What happens if you change the column formula to the below?

=IF(Difference@row * [Current Sell]@row <> 0, MAX(Difference@row * [Current Sell]@row, 0))

Shawn Church

Thanks to everyone who was helping with this… I solved the problem. Somewhere in my hundreds of rows of data someone keyed a non-number into a cell causing an error in one cell of the Potential Loss column. That single error kept my sheet summary formula from working. It's been fixed and shouldn't happen again.

Paul Newcome

What is the formula populating the [Potential Loss] column?

Shawn Church

@Paul Newcome - this is the formula:

=IF(Difference@row * [Current Sell]@row = 0, "", IF(Difference@row * [Current Sell]@row < 0, 0, IF(Difference@row * [Current Sell]@row > 0, Difference@row * [Current Sell]@row)))

Isis Taylor

@Shawn Church This one is stumping me. I set up the same formula and received no error.

Paul Newcome

I wonder if it has to do with the blanks. What happens if you change the column formula to the below?

=IF(Difference@row * [Current Sell]@row <> 0, MAX(Difference@row * [Current Sell]@row, 0))

Shawn Church

Thanks to everyone who was helping with this… I solved the problem. Somewhere in my hundreds of rows of data someone keyed a non-number into a cell causing an error in one cell of the Potential Loss column. That single error kept my sheet summary formula from working. It's been fixed and shouldn't happen again.