#INVALID OPERATION (SUMIF/SUMIFS to blame?)

The formula I'm trying to fix was not reporting our information correctly so I built a new formula in a test environment and it appeared to be working as intended only for me to find that when applying it in a live setting, the result was constant #INVALID OPERATION errors.

The formula is this:

=IF([Est. Annual Rent]@row = 0, " ", IF(SUMIF([Project No.]:[Project No.], [Project No.]@row, [Est. Annual Rent]:[Est. Annual Rent]) < 280000, "LEVEL I", IF([Project Aggregate Total Contract Value]@row < 10000000, "LEVEL II", IF([Project Aggregate Total Contract Value]@row > 10000000, "LEVEL III"))))

I made another version two with a modification to the end of the formula to see if that would help (It didn't):

=IF([Est. Annual Rent]@row = 0, "", IF(SUMIF([Project No.]:[Project No.], [Project No.]@row, [Est. Annual Rent]:[Est. Annual Rent]) < 280000, "LEVEL I", IF([Project Aggregate Total Contract Value]@row < 10000000, "LEVEL II", "LEVEL III")))

What I've Tried:

In both the testing and live environments, all the column types are Text/Number.
I switched the SUMIF to SUMIFS (As well as switching the range/criteria ranges to match what the function calls for)
Rewriting the formulas from scratch to ensure syntax wasn't the issue (It still may be)

Screen Shots included to show column structure and errors

Working Capture.PNG

Broken Capture.PNG

Find more posts tagged with

Formulas

Government

Real Estate

Comments

Alejandra

Hello,

I wasn't seeing an issue with the operators at first, so I created a sheet with the same column names and then pasted your formula in, but it worked just fine.

I went ahead and reached out to our Support team so they can refresh your sheets just in case that helps. If you're still receiving the #INVALID OPERATION error I'd recommend reaching out to our Support team directly so they can help you troubleshoot.

Sign in to join the conversation!

Quick Links

Trending discussions

Information in a form populates specific cell.
Hello, Just investigating an idea. I'm building a staff register sheet. I'm curious to know if its possible to populate specific cells by the answers given in a form? So if, in a form, a staff member gives their name, the date and that they will be on leave, sick, training ect. Once the form is submitted, it will populate…
A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?