COUNTIFS & #INVALID REF Error

Veronica Ruff

Hi! I built a rollup sheet with the following formulas. They work, but when a row is deleted (probably added too), the formula on the reference sheet the rollup sheet causes the #INVALID REF error. I have to manually edit the formula to get it to work again. One section has about 50 rows and 3 columns of formulas.

=COUNTIFS({Partner Certification Tracking CORE Status}, $Status$8, {Partner Certification Tracking core cert date}, AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 12, 31)))

=COUNTIFS({Partner Certification Tracking CORE Status}, $Status$13, {Partner Certification Tracking Sheet Partner}, OR(CONTAINS("Mcbride", @cell)))

Thanks for any help on how to avoid this.

Andrée Starå

Hi Veronica,

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

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!