Formula for Counting RYG by Child Row
Hello - I'm trying to add to a Dashboard. I need to see status by color for each child row. I also need a count by each child row (Admin, CPM, etc.). Help! Thank you.
Best Answer
-
L_123 ✭✭✭✭✭✭
Alright, I understand you now.
The easiest way to do this is to add a helper column on your first sheet. Then use this formula and drag it all the way down.
=iferror(parent([initiative / milestone details]@row),"")
Then you can use your countifs
=countifs({Helper},[Primary Column]@row,{Status},"Red")
or something like that.
Answers
-
L_123 ✭✭✭✭✭✭
You mean you want each child to show on the dashboard? Do you have your information posted to a report?
-
Staci Britton ✭✭✭
I only want to see the status (red, yellow, green, or gray) for each child row. One child row might have one entry with the status as yellow. Additional child rows might have rows with three reds, and or 10 greens by row. I'm trying to reference a sheet as well. I tried this but I'm sure I'm way off:
=COUNTIFS(CHILDREN(){QSP Initiatives Range 1}, Red2, {QSP Initiatives Range 1}, Yellow2, {QSP Initiatives Range 1}, Green2, {QSP Initiatives Range 1}, Grey2))
Thanks for your help!
-
L_123 ✭✭✭✭✭✭
something like this?
="Green: " + countif(children(),"Green ")+"Yellow: " + countif(children(),"Yellow"),+"Red: " + countif(children(),"Red") + "Grey: " + countif(children(),"Grey")
-
Staci Britton ✭✭✭
I've used this formula:
=COUNTIFS({Level}, Red$1, {Level}, Yellow$1, {Level}, Green$1, {Level}, Grey$1)
我得到#s on my destination sheet but not the correct numbers from status column on the reference sheet.
Reference Sheet:
Destination Sheet:
If you see Admin, it has one yellow - that does not appear under Admin-Yellow. CPM has at least two green and a grey - they do not appear on the CPM line under green or grey.
I think I'm close but don't know what I'm missing.
Thank you for your help[email protected]!!
-
L_123 ✭✭✭✭✭✭
Alright, I understand you now.
The easiest way to do this is to add a helper column on your first sheet. Then use this formula and drag it all the way down.
=iferror(parent([initiative / milestone details]@row),"")
Then you can use your countifs
=countifs({Helper},[Primary Column]@row,{Status},"Red")
or something like that.
-
Staci Britton ✭✭✭
THANK YOU!!!!! That worked!! Whooop!
-
Staci Britton ✭✭✭
It's actually not reading the collapsed rows. How would I fix this?
-
L_123 ✭✭✭✭✭✭
Right click the primary column and select "Expand all". Then drag your helper formula down across all items. Your helper column wasn't copied to rows you dragged it across if they were collapsed. You can them re-collapse your rows and it should work.
-
Staci Britton ✭✭✭
It's not reading these rows...
-
L_123 ✭✭✭✭✭✭
Do you have the exact same spelling/spacing in the primary column in your lookup sheet?
Help Article Resources
Categories
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/69131/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":36,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"