Exclude summary row from count

RGeet

Hello,

How do I exclude the summary row in the following formula?

=" Yellow: " + COUNTIF(Risk:Risk, "Yellow") + " Red: " + COUNTIF(Risk:Risk, "Red")

Basically, the summary row RYG ball risk indicator should NOT be included in the risk count of the child rows because it causes a count that is one higher than the actual count.

Thanks in advance for any ideas.

 

Paul Newcome

Try changing this

COUNTIF(Risk:Risk, "Yellow")

to this

COUNTIFS(Risk:Risk, AND(@cell = "Yellow", COUNT(CHILDREN(@cell)) = 0))

 

The same can be done for the "Red" section.

RGeet

Thanks Paul for replying. I just gave that a try and it is returning "#unparseable." I used the following:

=" Yellow: " + COUNTIFS(Risk:Risk, AND(@cell = "Yellow", COUNT(CHILDREN(@cell)) = 0)) + " Red: " + COUNTIFS(Risk:Risk, AND(@cell = "Red", COUNT(CHILDREN(@cell)) = 0))

 

 

Paul Newcome

My apologies. Try removing the @cell reference when counting the children

 

=" Yellow: " + COUNTIFS(Risk:Risk, AND(@cell = "Yellow", COUNT(CHILDREN()) = 0)) + " Red: " + COUNTIFS(Risk:Risk, AND(@cell = "Red", COUNT(CHILDREN()) = 0))

RGeet

No problem, thank you for trying to help me solve this. It is still capturing one extra and I noticed something else, grandparent rows are being captured also inflating the counts.

Therefore, not only do I need to exclude the project summary row but, also grandparent rows (if I am using that term correctly). There is a project summary row and multiple grandparent rows with grandchildren. I have included an image (clipping of the larger project schedule).

In the image example:  There is actually only one task that is Risk Red (there is a formula in the risk column that changes the project summary row Risk yellow if there is any yellow but no red or red if there is one red regardless of the number of yellows). In the "Project Risk Counts" column the total of Risk Red items is incorrectly showing as 3 because the grandparent row and the project summary row are being included in the count. The count should just be 1 Red. 

 

Paul Newcome

Are you able to show the Risk, Count, and Task name columns for the full range? Based on the formula it shouldn't be counting any cells that have children rows, so I am wondering if maybe there isn't an issue within the hierarchies somewhere.

RGeet

I reworked this testing SmartSheet to show the full range of items that actually have a risk value. The other tasks have no risk assignment and shouldn't affect the counts.

In this example, the "...Counts" column incorrectly has Yellow 3 (should be 2) and Red 3 (should be 1).

 

Paul Newcome

Try this... Create a checkbox column called Child. Enter

 

=IF(COUNT(CHILDREN([Task Name]@row)) = 0, 1)

 

Dragfill that down.

 

Then change the formula in the Counts column to

 

=" Yellow: " + COUNTIFS(Risk:Risk, @cell = "Yellow", Child:Child, @cell = 1) + " Red: " + COUNTIFS(Risk:Risk, @cell = "Red", Child:Child, @cell = 1)

RGeet

Paul you're a Smartsheet formula genius! This solution works perfectly! 

How do people using Smartsheet learn how to write these formulas?? I would have never figured this out (though I am new to using formulas).

Many thanks and much appreciated.

Paul Newcome

Haha. I wouldn't go so far as to say "genius", but thank you. I'm happy to help. 

 

My knowledge in Smartsheet has come out of necessity. My VP came to me one day about a year ago and said I was going to be the lead in adopting Smartsheet across all departments. I'd never even heard of it before and only had basic experience in Excel years before.

 

My people wanted automation. If they had to do everything manually, then why not just keep using Excel? Logical question. So I started plugging away trying to learn everything I could about automation and making people's jobs easier.

 

I started out with some VERY basic stuff, but my curiosity (and desire for a paycheck) got the better of me. I started experimenting with formulas to see what would work and what wouldn't. I have spent MANY days beating my head against a wall only to find the solution was MUCH more simple than I thought it was going to be and had been staring me right in the face the whole time.

 

I got my feet wet with "complex" formulas (more than just your basic functions) by watching a video on how to automate RYG balls using a nested IF statement. From there I found that thinking outside of the box, patience, and plenty of trial and error can result in some pretty interesting solutions.

 

I found the 3 resources I used most (and still use today) are:

1. The Community. There is A TON of knowledge to be found here and most of the people are pretty great, friendly, and helpful.

 

2. The below link. It is a page that lists out all possible errors you can come across when building formulas along with what causes them and some trouble shooting tips.

https://help.smartsheet.com/articles/2476176-formula-error-messages?_ga=2.66067626.529420994.1554725353-1302373248.1552411124

 

3. There is a template in the Solution Center called "Smartsheet Formula Examples". It is a GREAT tool. It is an interactive sheet with all of the different functions listed out along with examples and descriptions. You can play around with the functions to get a feel for how they work, and if you accidentally mess something up and can't fix it... Just delete the sheet and download a new template.

.

A few tips for working on more complex formulas:

1. Break it down into as small of pieces as you can; down to the most very basic functions.

2. Use helper columns, rows, or cells to spread those pieces out. This will allow you to make sure that every piece is working. If something is NOT working, you will be able to hone in on the problem very quickly.

3. BE PATIENT. 

4. Use your resources. Not only the list above, but other people as well. I have found that sometimes I was over thinking a solution just to have a coworker that knows nothing about formulas accidentally give me the answer I was looking for. Simple things like "Why can't you just add these things together?" or "Is it possible to just do xyz?" which garners a response of "DUH! Thank You!!"

5. Don't be afraid to leave those helper columns. You don't have to combine them into crazy monster formulas that are easy to break and impossible to troubleshoot. The helper columns can be hidden to keep you sheet looking clean, and people will think you're some kind of magician. Haha.

 

As an example... I have a sheet where only 3 columns are show and only two of those are used. The first column is a listing that is locked. The second column is a date column for the top half of the sheet, and the third column is checkboxes for the bottom half of the sheet.

 

I have an additional 102 hidden columns with formulas and tables galore used to manipulate dates based off of the few cells that are actually manually updated. Granted... This is not a "one time use" sheet either. We have used it as a template over 100 times already this year and I am still making tweaks here and there.

 

6. Find a good solid wall. You will need something to bang your head against, and your keyboard will get in the way if you try to use the desk. 

 

Haha. Long story short. Patience, trial and error, time, and experience will all eventually play a role in your formula writing capabilities. You should see some of my first posts here in the Community. The solution to one of my questions was to add two cells together and divide it by a set number...

 

You'll get there. 

RGeet

Thank you very much for all that information and encouragement. Literally, all of that is/will be helpful including the tip to find an appropriate wall for head banging. LOL

Paul Newcome

Hahaha. Happy to help.