#INVALID OPERATION Error

Amy Foeller

Question: Is it possible to write a SUMIFS column formula to add up specific cells in an adjacent column where the criteria are also in other adjacent columns? (Actually, I know it's possible, because the formula I wrote a couple days ago worked just fine at first, but it's refusing to work today, and I don't know why. The formula is returning an #INVALID OPERATION error today.)

Context: I have a sheet with chunks of rows for ongoing weeks throughout the year. Each week "chunk" has a header row, data rows, and a total row. (The data rows and total row are children of the header row.) The header row for the following week is directly below the "child" total row of the previous week, so there are no breaks in the data.

What I need: I need a column formula to look at all the prior weeks and add up the rows where the job name matches the job name in the current row. I also need the formula to include the amount in the current week for that job. (This amount is in a different column of the same row as the formula in question.) For example, say I'm looking at Job X in Week 3. I need the Column Y formula in the Job X row of Week 3 to look at Column Z of Week 1, Week 2, and Week 3 and add up those values for Job X in all three weeks.

My whole formula is pretty complicated. Below is a simplified version of the portion I believe is prompting the #INVALID OPERATION error. For this example, the formula is in Column Y. Column Z and the [Invoice Amount] columns are text/number columns formatted as ($) currency. The [Job Name] column is formatted as text. The [Week #] column contains the week number (1-52) of that week.

=IF(SUMIFS([Column Z]:[Column Z], [Job Name]:[Job Name], [Job Name]@row, [Week #]:[Week #], <= [Week #]@row) > [Invoice Amount]@row, [Invoice Amount]@row - SUMIFS([Column Z]:[Column Z], [Job Name]:[Job Name], [Job Name]@row, [Week #]:[Week #], <= [Week #]@row), 0)

This formula checks if the sum of the amounts in Column Z for Job X is greater than the actual invoice amount for Job X. It then subtracts the too-large sum in Column Z from the actual invoice amount.

I know that my "less than or equal to" operators are correct, and I also know that this formula worked previously. (As in, as recently as yesterday.) I simply can't figure out why I'm getting an error now. Is there another workaround to SUMIF the weeks less than or equal to the current week?

I realize I can use absolute references to make this formula work. However, if I use absolute references, I can't convert this formula to a column formula. This formula is in a hidden column that I don't want to have to un-hide every week to extend the formulas down when I add new data.

Thanks!

Amy Foeller

Never mind, all. I FIGURED IT OUT MYSELF. *yay*

I started over with blank columns and re-wrote the formulas. Sure enough, they worked again, just as they had the first time I wrote them.

For anyone who may be curious to know the valuable tidbit of information below that I spent WAY too much time and effort (including, but not limited to, curses, tears, and fits of pure rage) trying to figure out...

The original #INVALID OPERATION error was caused by a FORMATTING issue with a date column. Apparently, certain formulas don't like to reference date columns that have hyphen "-" placeholders in them. Who knew? Well, I KNOW NOW. (Bleh.)

*bows*

Amy Foeller

Update: I'm getting another #INVALID OPERATION error when I try to add the number in my [Invoice Amount] column to the SUMIFS result. The SUMIFS result works by itself, and the [Invoice Amount] number isn't returning an error. I should be able to simply SUM them together, but NOPE. WHY?!

Side note to this whole fiasco: I would LOVE to see a formula auditing capability added to smartsheet. Excel has this capability, and I use it all the time to test/audit my more complex formulas. Trying to figure out WHY this particular formula is giving me hell is basically impossible without being able to audit each step of the formula calculation. Yes, I've already submitted "Enhancement Requests" for this, but I'm impatient, and dealing with this is a huge waste of time. I could have figured this out in Excel in less than five minutes, yet I've been working on this in smartsheet ALL day today. Grr.

Andrée Starå

Hi @Amy Foeller

I hope you're well and safe!

Strange!

I tested the formula, and it seems to work for me.

I'd be happy to take a quick look.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots and the complete formula? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Amy Foeller

Hi Andrée, thank you for your help!

Which formula did you test? The one in my original question? Did it work as a column formula?

=IF(SUMIFS([Column Z]:[Column Z], [Job Name]:[Job Name], [Job Name]@row, [Week #]:[Week #], <= [Week #]@row) > [Invoice Amount]@row, [Invoice Amount]@row - SUMIFS([Column Z]:[Column Z], [Job Name]:[Job Name], [Job Name]@row, [Week #]:[Week #], <= [Week #]@row), 0)

In more detail: Our painting company tracks our jobs week by week. At the beginning of a job, we have a budget that calculates an estimated invoice amount for when the job is finished. As the job progresses each week, we account for a portion of that calculated invoice amount by multiplying our actual production hours times an hourly tracking rate and adding in materials and other job-related costs. (T&M) The ongoing revenue calculation needs to be capped at the estimated invoice amount UNTIL the job completes. Once a job is completed, the final invoice is generated, which is our actual revenue for that job. At this point, the ongoing revenue calculation needs to look at the actual invoice amount, compare it to the revenue that was accounted for in the previous weeks, and calculate the difference, whether it be a positive or negative amount. This final amount constitutes the revenue for the final week of the job.

Example: The Smith job has an estimated invoice amount of $4,000 based on the budget. In Week 1, based on actual hours/costs, we account for $2,300 of revenue. In Week 2, the T&M calculation wants to account for an additional $2,400 of revenue. However, since that would bring the total job revenue to $4,700, we need to subtract $700 from Week 2's calculated revenue in order to get a final Week 2 revenue of $1,700. In Week 3, the T&M calculation wants to account for an additional $2,900 of revenue. Since the estimated invoice amount of $4,000 was already captured completely in Weeks 1 and 2, Week 3's revenue must be $0. The job completes in Week 4 and the final invoice is generated, which is $5,000. The T&M calculation for Week 4 only wants to account for $200, so we need to add an additional $800 of revenue to make the entire job's revenue equal the final invoice amount of $5,000. To recap, Week 1 final revenue is $2,300 ($2,300 T&M + $0 Adjustment), Week 2 final revenue is $1,700 ($2,400 T&M - $700 Adjustment), Week 3 final revenue is $0 ($,2,900 T&M - $2,900 Adjustment), and Week 4 final revenue is $1,000 ($200 T&M + $800 Adjustment).

As you can see, between the initial T&M calculation, the adjustment, and the final weekly revenue amount, I am having an incredibly difficult time getting the later weeks of the job to calculate their revenue correctly. I get a circular reference when I try to use column formulas. The Final Weekly Revenue column SUMs the T&M and Adjustment columns. In future weeks, the Adjustment column looks back at the previous weeks' Final Weekly Revenue column to make the necessary adjustment for the current week. This gives me a circular reference even though the formula is only SUMIFS-ing the previous weeks' Final Weekly Revenue column, NOT the current week's Final Weekly Revenue column, which is based on the Adjustment column.

I also tried doing this entire calculation in a single column. Smartsheet doesn't like that either, because the formula has to look back and find the previous weeks' revenues in order to calculate the current week's revenue correctly. Since the formula is in the same column, BAM. #CIRCULAR REFERENCE.

I could do this without issue in Excel because I used the OFFSET and MATCH functions to look at the specific range I wanted and avoid circular references. Since OFFSET isn't available in smartsheet, I'm doing my best to figure out another way.

Any additional insight you can provide would be greatly appreciated!

Paul Newcome

I have found that dates can get a little touchy when being used like this, and sometimes using "@cell" references clears things up.

=IF(SUMIFS([Column Z]:[Column Z], [Job Name]:[Job Name], @cell = [Job Name]@row, [Week #]:[Week #], @cell <= [Week #]@row) > [Invoice Amount]@row, [Invoice Amount]@row - SUMIFS([Column Z]:[Column Z], [Job Name]:[Job Name], @cell = [Job Name]@row, [Week #]:[Week #], @cell <= [Week #]@row), 0)

Amy Foeller

This didn't work either.

@Paul Newcome, do you have any suggestion for my question above your original response?

Paul Newcome

Exactly what is your question?

Amy Foeller

@Paul Newcome

In more detail: Our painting company tracks our jobs week by week. At the beginning of a job, we have a budget that calculates an estimated invoice amount for when the job is finished. As the job progresses each week, we account for a portion of that calculated invoice amount by multiplying our actual production hours times an hourly tracking rate and adding in materials and other job-related costs. (T&M) The ongoing revenue calculation needs to be capped at the estimated invoice amount UNTIL the job completes. Once a job is completed, the final invoice is generated, which is our actual revenue for that job. At this point, the ongoing revenue calculation needs to look at the actual invoice amount, compare it to the revenue that was accounted for in the previous weeks, and calculate the difference, whether it be a positive or negative amount. This final amount constitutes the revenue for the final week of the job.

Example: The Smith job has an estimated invoice amount of $4,000 based on the budget. In Week 1, based on actual hours/costs, we account for $2,300 of revenue. In Week 2, the T&M calculation wants to account for an additional $2,400 of revenue. However, since that would bring the total job revenue to $4,700, we need to subtract $700 from Week 2's calculated revenue in order to get a final Week 2 revenue of $1,700. In Week 3, the T&M calculation wants to account for an additional $2,900 of revenue. Since the estimated invoice amount of $4,000 was already captured completely in Weeks 1 and 2, Week 3's revenue must be $0. The job completes in Week 4 and the final invoice is generated, which is $5,000. The T&M calculation for Week 4 only wants to account for $200, so we need to add an additional $800 of revenue to make the entire job's revenue equal the final invoice amount of $5,000. To recap, Week 1 final revenue is $2,300 ($2,300 T&M + $0 Adjustment), Week 2 final revenue is $1,700 ($2,400 T&M - $700 Adjustment), Week 3 final revenue is $0 ($,2,900 T&M - $2,900 Adjustment), and Week 4 final revenue is $1,000 ($200 T&M + $800 Adjustment).

As you can see, between the initial T&M calculation, the adjustment, and the final weekly revenue amount, I am having an incredibly difficult time getting the later weeks of the job to calculate their revenue correctly. I get a circular reference when I try to use column formulas. The Final Weekly Revenue column SUMs the T&M and Adjustment columns. In future weeks, the Adjustment column looks back at the previous weeks' Final Weekly Revenue column to make the necessary adjustment for the current week. This gives me a circular reference even though the formula is only SUMIFS-ing the previous weeks' Final Weekly Revenue column, NOT the current week's Final Weekly Revenue column, which is based on the Adjustment column.

I also tried doing this entire calculation in a single column. Smartsheet doesn't like that either, because the formula has to look back and find the previous weeks' revenues in order to calculate the current week's revenue correctly. Since the formula is in the same column, BAM. #CIRCULAR REFERENCE.

I could do this without issue in Excel because I used the OFFSET and MATCH functions to look at the specific range I wanted and avoid circular references. Since OFFSET isn't available in smartsheet, I'm doing my best to figure out another way.

Any additional insight you can provide would be greatly appreciated!

Paul Newcome

Are you able to provide some screenshots for reference with sensitive/confidential data removed, blocked, and/or replaced with mock data as needed?

Amy Foeller

I'm not sure how helpful screenshots will be since I explained everything above, but here is one.

The three Ongoing Revenue columns calculate the revenue generated for each job each week. The T&M (Time and Materials) column multiplies the actual hours times an hourly tracking rate and then adds in the additional costs for materials and subcontractors. The Adjustment column is supposed to calculate any adjustment necessary. (See my "Example" explanation above for more details.) The Final column SUMs the T&M column and the Adjustment column to get a true, final revenue number for each job each week.

I'm having trouble with the formula in the "Adjustment" column. (The yellow cell in the screenshot above is the adjustment for the final week of the Kelly job, and it's not calculating correctly.) This formula needs to look at the previous weeks of each job, SUM the final revenue numbers for each week, compare that amount to either the estimated invoice amount (if the job hasn't been completed) or the actual invoice amount, and return the difference between the previous weeks' final revenues and the T&M amount for the current week. This way, the final revenue number for each job will ultimately match the final invoice amount.

The problem occurs because the Final formula is initially dependent on the Adjustment formula in the first week of any job, but the SUBSEQUENT Adjustment formulas need to take into account the PREVIOUS Final formula results in the adjustments for the following weeks of each job. In Excel, I would circumvent this potential circular reference by utilizing the OFFSET function to specify the exact range I want the adjustment formula to look at. Since OFFSET isn't available in smartsheet, I'm trying to figure out another way. Is there another way?

This whole thing is especially frustrating because, last week when I first wrote out these formulas, they worked just fine. However, the next day, I started getting the #INVALID OPERATION error. In trying to fix that, I now can't get around the #CIRCULAR REFERENCE error. I feel like this whole fiasco was caused by a formatting snag, but I don't know what it was. (I know my operators were correct in my original formulas.)

I would love for this formula to be a column formula so I don't have to keep copying it down as I add new data every week, but I would settle for using absolute references if they can make the formula actually work the way I need it to.

Any suggestions you might have will be appreciated!

Paul Newcome

Being able to visualize alongside the explanation is helpful because it shows your layout. There are a number of ways the data could be laid out based on reading your example alone and each could require different solutions.

Where do you store the data for the estimated vs actual invoice amounts? How do you indicate that a job is complete so that we know to pull the actual invoice vs the estimated invoice?

Amy Foeller

All of the pieces of information you asked about are in other columns in this particular sheet. The estimated and actual invoice amounts are formulas pulled from other sheets. I have a couple helper columns that indicate whether the job was completed during each week, and also the date that each job was actually completed.

Amy Foeller

Never mind, all. I FIGURED IT OUT MYSELF. *yay*

I started over with blank columns and re-wrote the formulas. Sure enough, they worked again, just as they had the first time I wrote them.

For anyone who may be curious to know the valuable tidbit of information below that I spent WAY too much time and effort (including, but not limited to, curses, tears, and fits of pure rage) trying to figure out...

The original #INVALID OPERATION error was caused by a FORMATTING issue with a date column. Apparently, certain formulas don't like to reference date columns that have hyphen "-" placeholders in them. Who knew? Well, I KNOW NOW. (Bleh.)

*bows*

Andrée Starå

@Amy Foeller

Excellent!

Glad you got it working!