Challenges with Summary Data Formulas/Dashboard Sometimes working

MichelleN

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 pre-populates from the first sheet using formulas, which is great until I try to make summary data/dashboard info from the formula-pulled 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 Smartsheet-all 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.

Brian_Richardson

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.

Brian_Richardson

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.

Brian_Richardson

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.

MichelleN

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.

Brian_Richardson

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.

MichelleN

Ahh, thank you! I've added the "IFERROR" wrap where data is being pulled from other sheets. That's great.