I'm getting #NoMatch error on AVERAGEIF

Dave Slavens

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.

Working:

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.

Dave Slavens

Never mind, I found the issue. It was buried in another Epic that was throwing the error but based on the formulas it causes all Epic to throw the error.

Dave Slavens

Never mind, I found the issue. It was buried in another Epic that was throwing the error but based on the formulas it causes all Epic to throw the error.