I'm trying to average the value of Opportunity Age (Days) in a cell when Sales Stage contains "Won".

I'm receiving an #INVALID OPERATOR for the following:

=AVERAGEIF([Opportunity Age (Days)]:[Opportunity Age (Days)], [Sales Stage]:[Sales Stage], (CONTAINS("Won", @cell)))

Any tips you could provide would be greatly appreciated - thank you.

Best Answer



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The only thing I can see right off is a set of parenthesis that isn't needed. Does it work if we remove those as below?

    =AVERAGEIF([Opportunity Age (Days)]:[Opportunity Age (Days)], [Sales Stage]:[Sales Stage], CONTAINS("Won", @cell))

  • Hi Paul,

    I tried removing the Paranthesis - but still shows as #INVALID OPERATION.

    Would i be better off trying to use Collect?

    Here is an image:

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    May be this?

    =AVERAGEIF([Opportunity Age (Days)]:[Opportunity Age (Days)], [Sales Stage]:[Sales Stage], @cell = "Won")

    Is that particular error present in either of the two columns being evaluated?

  • Hi Paul,

    Still getting #INVALID OPERATION for

    =AVERAGEIF([Opportunity Age (Days)]:[Opportunity Age (Days)], [Sales Stage]:[Sales Stage], @cell = "Won")

    The formula is in an added field in the Sheet Summary - So not sure if error is present in either or both columns.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Double check the columns. If that error is present in even one cell being referenced by the formula, it will pull through.

  • Hi paul, thank you for all the help - the image below is what I'm looking at. Forgive my ignorance - how would i determine if the error is present in even one cell of those columns being referenced?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The error would be visible the same way it is visible in the sheet summary field, but I don't see it in any cells in your screenshot.

    Try deleting the formula and the Sheet Summary field then creating a new field and manually typing the formula back in.

  • Hi Paul - I went ahead and deleted the formula and the Sheet Summary Field then I created a new field and manually typed the formula back in. I received the same error #INVALID OPERATION.

    However, I decided to try this in the new field:

    =AVG(COLLECT([Opportunity Age (Days)]:[Opportunity Age (Days)], [Sales Stage]:[Sales Stage], "Won"))

    and the formula worked like a charm!

    I have no idea why

    =AVERAGEIF([Opportunity Age (Days)]:[Opportunity Age (Days)], [Sales Stage]:[Sales Stage], @cell = "Won")

    is still returning #INVALID OPERATION,

    but I'll go with =AVG(COLLECT([Opportunity Age (Days)]:[Opportunity Age (Days)], [Sales Stage]:[Sales Stage], "Won")) for the Win!

    Thank you for all your help! I'm really puzzled why the original =AVERAGEIF didn't work.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I am not sure why the AVERAGEIF wasn't working. The AVG/COLLECT was going to be my next idea if we were unable to get your original squared away.

  • Thanks so much for your help Paul

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!