I'm getting #NoMatch error on AVERAGEIF

I am using the Smartsheet JIRA connector to pull data into my sheet then I'm performing multiple calculations to get the % Complete for Stories (triple weighted score). I'm taking that calculation then rolling it up to the Epic (for all Stories) then the Theme (for all Epics).

Formula for rolling up Stories to Epics:

=IF([Issue Type]@row = "Epic", ROUND(AVERAGEIF([Epic Link]:[Epic Link], [Issue Key]@row, [PC - Work]:[PC - Work]), 2), "--")

I'm creating a sheet for each Theme, this formula works great on 98% of the sheets, however every once in a while I'm getting a sheet that throws a #NOMATCH error.


Rollup Columns

Doesn't Work

Rollup Columns

The questions I have are:

1) Why does it work most of the time but fail periodically?

2) What can I do to keep this from happening?

It's happening on some other columns with COUNTIFS too but I'm thinking that the same issue as described as above is happening there too. I'm hoping that if I can resolve this issue the solution will help to resolve those issues too.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!