Using Count ifs to Check 3 columns and validations for a dashboard.

Using Countifs to Check Origin of error with the reference to the month and validation already provided in the data. Everytime I complete the first criteria the option to select the column from reference sheet gets disabled. Please suggest a workaround.
Answers
-
Here are the columns:
-
I entered this, =COUNTIFS({UMMS Error Resolution Tracker Range 5},[Column3]51), ({UMMS Error Resolution Tracker Range 6}, [Column2]@row), ({UMMS Error Resolution Tracker Range 7, "True"})) Got Unparseble and then ran AI , got this formula:
=COUNTIFS({UMMS Error Resolution Tracker Range 5}, [Column3]51, {UMMS Error Resolution Tracker Range 6}, [Column2]@row, {UMMS Error Resolution Tracker Range 7}, "True")
Which is giving a new error, #Invalid Ref, in capitals, So Rude!
-
Hi @Devg
In the demo solution sheet below, I used the following to use the COUNTIFS function.
[Dec] =COUNTIFS({UMMS Error Resolution Tracker : How}, [How was the error found?]@row, {UMMS Error Resolution Tracker : Date}, MONTH(@cell) = 12, {UMMS Error Resolution Tracker : Verified}, true)
[Jan] =COUNTIFS({UMMS Error Resolution Tracker : How}, [How was the error found?]@row, {UMMS Error Resolution Tracker : Date}, MONTH(@cell) = 1, {UMMS Error Resolution Tracker : Verified}, true)Explanation of the Formula:
The formula calculates the number of rows in a reference Smartsheet that meet three specific conditions:
How was the error found?
matches the current row's value in the column[How was the error found?]@row
.- The error occurred in the specified month (
December
orJanuary
) based on theDate
column. Verified
is marked astrue
.
Here’s a breakdown of each formula:
Formula for December:
[Dec] =COUNTIFS({UMMS Error Resolution Tracker : How}, [How was the error found?]@row, {UMMS Error Resolution Tracker : Date}, MONTH(@cell) = 12, {UMMS Error Resolution Tracker : Verified}, true)
{UMMS Error Resolution Tracker : How}
: References the "How was the error found?" column in the source sheet.[How was the error found?]@row
: Refers to the current row's value for comparison.{UMMS Error Resolution Tracker : Date}
: References the date column in the source sheet.MONTH(@cell) = 12
: Ensures only rows where the date is in December are counted.{UMMS Error Resolution Tracker : Verified}
: References the "Verified" column in the source sheet.true
: Counts rows where the "Verified" checkbox is checked.
In the above formula, @cell in the MONTH(@cell) condition put the data values of the range {UMMS Error Resolution Tracker : Date} one by one for each row.
In the last condition, true is a special logic value. So, do not put "" around it like "true". Alternatively, you can put 1 to stand for true.
For January, you change the MONTH(@cell) = 12 to MONTH(@cell) = 1.
This is a sample data sheet based on your description. Note the cause -"Audit - …" and Verified Error checked counts 5, which matches the calculated result above.
(Source Sheet)
-
Thanks a Ton!, I was going wrong with entering another bracket at the end of the 2nd query which allowed me to select additional ranges.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!