Challenges with Summary Data Formulas/Dashboard Sometimes working
I have a first sheet that was filled out by people using a smartsheet form to populate a sheet for a certification program. A second program is only open to the people who were already certified in the first program, so they fill out a Smartsheet form with minimal fields, include a unique ID number, and the rest of their info prepopulates from the first sheet using formulas, which is great until I try to make summary data/dashboard info from the formulapulled data.
Specifically I pulled their Division from the first sheet to the second one. I want to know how many people from each division (division is pulled by a formula) signed up for the second program and to include it in a dashboad. I wrote summary formulas (e.g.,
=COUNTIFS($Division:$Division, "Library/Learning Resources")
Then I added that summary data on a dashboard. On my work computer or the downloaded version of Smartsheetall the summary results for each division say "NO MATCH" On my home computer, they work and give totals. So I never know when I show to managers if it will actually show the data or look broken, or what the managers will see if I share the Dashboard link. why does it work, but only sometimes? I haven't figured out another way to summarize the data.
Best Answers

It's because some row in your second data sheet is not finding the Division name from the first sheet, likely the ID number doesn't exist or got entered wrong. When one part of the "chain of data" throws an error, then downstream formulas pick up the same error.
For the Division lookup column in your second sheet, wrap your INDEX/MATCH with an IFERROR like this:
=IFERROR(INDEX({Division},MATCH(ID@row,ID:ID,0)),"")
(replace whatever column names and references you're using)Your COUNTIF should then stop reporting the No Match error from the Division column.
Also, minor nitpick but in your COUNTIFS formula you don't need the leading $ and since you have only 1 criteria you don't need COUNTIFS. Instead
=COUNTIF(Division:Division,"Library/Learning Resources")
.One alternative to running a calculation and using that for reporting would be to put a report up that groups your data on the Division and add a Summarize to count the entries. Then you'll have a report showing all your results grouped and added up, which you can put directly on the dash and/or make a chart from it.

Yes I'm pretty sure that's what's happening.. because your summary formula is sweeping all the way down the column, by design, it picks up all the values including errors. If any row contains an error, then it cannot complete the sum/count/whatever and just passes the error through.
That's why you want to replace the errors in the Division column with blanks by using the IFERROR function. Then, when your summary formula tries to sweep through that column, it won't get caught with errors because there will be none.
Answers

It's because some row in your second data sheet is not finding the Division name from the first sheet, likely the ID number doesn't exist or got entered wrong. When one part of the "chain of data" throws an error, then downstream formulas pick up the same error.
For the Division lookup column in your second sheet, wrap your INDEX/MATCH with an IFERROR like this:
=IFERROR(INDEX({Division},MATCH(ID@row,ID:ID,0)),"")
(replace whatever column names and references you're using)Your COUNTIF should then stop reporting the No Match error from the Division column.
Also, minor nitpick but in your COUNTIFS formula you don't need the leading $ and since you have only 1 criteria you don't need COUNTIFS. Instead
=COUNTIF(Division:Division,"Library/Learning Resources")
.One alternative to running a calculation and using that for reporting would be to put a report up that groups your data on the Division and add a Summarize to count the entries. Then you'll have a report showing all your results grouped and added up, which you can put directly on the dash and/or make a chart from it.

Hi Brian, thanks for responding to my question. However, I think maybe I didn't ask my question correctly.
Yes, there were a few incorrectly entered ID numbers and I fixed those to properly fill the cell. I want to use SUMMARY data in the right sidebar data summary tool for the sheet. I want that summarized data to go onto a dashboard. I have the formulas set up (and you are right I copied a prior formula and didn't need the "s" on COUNTIF for that item, or the $).
Are you saying that if any Division fills with "NOMATCH" it will lead every division summary equation to fill with NO MATCH? Right now, people are still filling out the form, so perhaps that is the problem and as long as every field fills in properly, the summary equations will work properly.

Yes I'm pretty sure that's what's happening.. because your summary formula is sweeping all the way down the column, by design, it picks up all the values including errors. If any row contains an error, then it cannot complete the sum/count/whatever and just passes the error through.
That's why you want to replace the errors in the Division column with blanks by using the IFERROR function. Then, when your summary formula tries to sweep through that column, it won't get caught with errors because there will be none.

Ahh, thank you! I've added the "IFERROR" wrap where data is being pulled from other sheets. That's great.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!