Formula to calculate numbers of checked/unchecked occurrences in dashboard

Anna RDA

I'm trying to create a dashboard widget that shows the our number of media articles by project, broken into those that mention our organisation vs those that only mention the project.

Something along these lines, but instead of "Totals" / "Column 3" these would be RDA Mention / Project Mention

I created the above using this set up in a helper sheet:

However, I want to pull the data from this sheet with the first column indicating projects with a check next to them, and the second column the projects with the checkbox blank:

I can get the total number of project mentions with this formula: =COUNTIF({Media Mentions Overview 2024-2025 Range 5}, "Study Hub")

And I can also get the total number of checked boxes with this formula: =COUNTIF({Media Mentions Overview 2024-2025 Range 6}, "1")

But I can't figure out how to then only count instances for each project where the RDA Mention checkbox is either checked, or unchecked.

I've tried this formula, updating the reference range to include both columns, but to no avail: =COUNTIFS([Project]:[Project], "Study Hub", [RDA Mention]:[RDA Mention], 1)

I've also tried: =COUNTIFS({Project Range}, "Study Hub", {RDA Mention Range}, 1) and selecting both columns in the page for the reference range, but I'm not sure if I need to include row numbers somewhere here in the {Project Range} field.

Things are either coming back invalid ref or unparseable.

Am I trying to achieve something that can't be done, or do I need to set my sheet up another way? Any assistance would be very much appreciated 😊

PS - also going on leave for Christmas/NY at the end of this week, so apologies if I don't reply to anyone straight away.

Paul Newcome

Your last formula is the closest. I assume that one is getting the invalid reference error? If so, make sure you are creating all cross sheet references appropriately.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

.

Humashankar

That's a great point!

Anna RDA

Thanks for the swift response, Paul.

I'll try experimenting a bit more with the last formula. I tried quite a few variations so far, but not sure what I'm missing 😵‍💫

Paul Newcome

That last formula is the syntax you will need. Are you using that one exactly and creating each {Cross Sheet Reference} according to the instructions in my last post? If so, what error or output are you getting, because that should be exactly what you need?

Anna RDA

@Paul Newcome - I discovered I had to use the formula on the originating sheet, not the helper sheet. However, the widget won't let me set the data range on the originating sheet, so I'm currently working through the formula for my helper sheet which works with the widget.

I thought I had this in the bag - the first line did exactly what I needed. When I copied/pasted to the next line and updated the parameters though, it came back with an error message. However, I think I'm on the right path and am confident I can now get this sorted.

Thanks again for your insights.