#NO MATCH error on a Sheet Summary Formula


I'm guessing the problem is because the field I'm referencing is a formula and not a value, but would appreciate suggestions on how to handle this.

I want a sheet summary formula to return how many Variances exist for each VarianceCategory, so I can use this information in a report.

I've listed the formulas that I'm attempting, but they return #NO MATCH.




  • Frank B.
    Frank B. ✭✭✭✭✭

    Hi @Mary Farmer I tried this and it seemed to work on my summary:

    =COUNTIFS(Variance:Variance, <>"0", VarianceCategory:VarianceCategory, "AMB/EDI")

    I just added the "" around the 0, give it a try.


  • Mary Farmer
    Mary Farmer ✭✭✭✭

    Thanks @Frank B. but this didn't help. The formula works fine when I reference a column that isn't a formula. Perhaps I need to list out each 'responsible' separately in the COUNTIFS formula, instead of using the VarianceCategory...? Might that formula look something like the below in order to get a count of variances for the VarianceCategory of IS?

    When I try the below, I get #INCORRECT ARGUMENT SET.

    =COUNTIFS(Variance:Variance, <>0, Responsible:Responsible, "IS Network OPS", "RCM Engineering", "RCM IS")


  • Frank B.
    Frank B. ✭✭✭✭✭
    edited 05/10/23

    Hi @Mary Farmer how about we break it down to see what the issue is (I'm guessing it's the Variance value), try these separately and see if you get the expected results:

    =COUNTIF(VarianceCategory:VarianceCategory, "AMB/EDI") should result in 6

    =COUNTIF(Variance:Variance, <>"0") should result in 7

    =COUNTIF(Variance:Variance, <>0) should result in 7


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!