Creating Complex Report

Debbie Sawyer

@Paul Newcome

Yes - that does doesn't it! When I wrote the function last night I was working from the report screen grab not the sheet screen grab so I didn't know the columns were adjacent!

All good advice for you @Randy Van Winkle

Randy Van Winkle

Debbie's formula worked like a charm! I will try Paul's as well. Is there any benefit to using either one over the other?

Subsequent question, any idea why "< 2 Week Supply" wouldn't work in a COUNTIFS formula? Seems like the < symbol is throwing it off.

Paul Newcome

The benefit would be that one requires much less typing which in turn is less likely to break due to typos, and would be much easier to update if anything needs to be tweaked in the future.

I did some testing for your other question...

Using a COUNTIFS and directly referencing data that includes "<" such as "< 2 Week Supply" doesn't work. Odd, but...

FIND and CONTAINS can both register the "<" just fine.

So instead of

=COUNTIFS({Range}, "< 2 Week Supply")

try this

=COUNTIFS({Range}, CONTAINS("< 2 Week Supply", @cell))

That should get you your counts.

Debbie Sawyer

@Randy Van Winkle

With your comment about which formula has benefits over the other, I would say it really doesn't matter how you get to an answer as long as you understand the syntax involved in the formula (others would disagree!)

Pauls version is easier to type, a lot shorter and less prone to typos; but it relys on you keeping the columns within the range in order. Which is absolutely fine if your sheet definition is complete and you are satisfied that there are no more columns to add and you are happy with their placement. However, if you re-order your columns within the sheet, this function will keep the range in the formula as any column inbetween the first column defined to the last column defined irrespective of where the first and last end up and and what is inbetween. (So if you move columns around or add others which might use the same drop down values they would be included within your range)

My version is harder to type and prone to typos (although I used copy and paste a lot to help eradicate this) and even though it is longer, it wouldn't matter if the sheet is manuipulated after its creation. The columns could move and have others inserted in between them and it wouldn't effect the result.

Horses for courses. One isn't any better than the other as they both give you what you require. My main point would be use the version that you can follow and can maintain long term. 😉

Have fun with Smartsheet :)

Kind regards

Debbie

Randy Van Winkle

Countifs formula worked great as well. Thanks for both of your help. This sheet has been a great help to my organization.

Paul Newcome

Happy to help! 👍️