Additional Code to be added

mike.thorpe17421
mike.thorpe17421 ✭✭✭✭✭
edited 06/21/22 in Formulas and Functions

I have created the following formula to count the number of cells for 2 specific requirements

=COUNTIFS({Reference Sheet 1}, Country@row, {Reference Sheet 1}, Name of topic)

This gives me what I want but I now need to add new criteria that looks at another column which is a date and I only want to count where a date exists and secondly at another column that contains either the words "To be launched" or "No launch planned" or "On Hold"

Any help much appreciated

Many thanks


Mike

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would continue with the same syntax of range comma criteria.


    =COUNTIFS({Reference Sheet 1}, Country@row, {Reference Sheet 1}, Name of topic, {range}, criteria, {range}, criteria)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • mike.thorpe17421
    mike.thorpe17421 ✭✭✭✭✭

    Thanks Paul. managed to get this work when one criteria is selected from the same range. Couldnt get it to calculate where more than one criteria from the same range is selected. i.e.

    In the range there are the following criteria "To be launched" or "No launch planned" or "On Hold" as it appears to be looking for all of these or is it any of these.

    Also what is the formula when looking for anything to appear in the date field, i.e. it is not empty.

    Thanks again for your help.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I'm not sure if I responded before your update or if I misunderstood your original question.


    When there are multiple possibilities for the same range, you use an OR like so:

    =COUNTIFS({Range}, OR(@cell = "To be launched", @cell = "On Hold", @cell = "No launch planned"))


    To count for a cell containing any data (not empty):

    =COUNTIFS({Range}, @cell <> "")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!