Crosssheet Countifs using Date and OR

Karina.Perez
Karina.Perez ✭✭✭
edited 12/09/19 in Smartsheet Basics

Counting data from another sheet: 

Need instances of Class , if,

Date is >= Start date,

Date is <= End date,

Class needs to ="Webinar: AGM", "Webinar: Hybrid", "Webinar: ToolBox", or Webinar: Objections"

 

Columns = {Class} and {Date2}

Cell References= [Start Date]1 and [End Date]1

 

Formula says invalid operation.

=COUNTIFS({Class}, {,Date2}, >=[Start Date]1, {Date2}, <=[End Date]1, OR(@cell = "Webinar: AGM", @cell = "Webinar: Hybrid", @cell = "Webinar: ToolBox", @cell = "Webinar: Objections"))

Start and end date part works fine in other formulas. I think it's counting the individual class names that I cant get right.

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It is because you have no criteria set for the {Class} range. You would either need to get rid of it or establish some kind of criteria even if it is as simple as

    ISTEXT(@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

  • To add a bit to Paul's comment—there's an extra comma in your {Date} cross-sheet reference.

    This wouldn't return the #INVALID OPERATION error that you're getting right now but removing the comma will avoid you getting the #UNPARSEABLE error.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Thanks Shaine. I meant to mention that, got interrupted in the middle of my reply, and forgot to finish it out. If I am not mistaken, I believe that would throw an #INVALID REF error. I did this a few times when manually entering a previously used cross-sheet reference and getting it wrong. I have since gotten into the habit of using the "Reference Another Sheet" link in the formula helper box every time to avoid that. Hahaha.

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I also just realized that my initial response is actually incorrect. You do have the criteria for your {Class} range, but it is in the wrong place. If I am not mistaken I believe you meant for your OR statement to be your criteria for {Class}. If so, it should be directly after the range. Give this a shot...

     

    =COUNTIFS({Class}, OR(@cell = "Webinar: AGM", @cell = "Webinar: Hybrid", @cell = "Webinar: ToolBox", @cell = "Webinar: Objections"), {Date2}, >=[Start Date]1, {Date2}, <=[End Date]1)

    .

    P.S.

    You can also use the AND function for your {Date2} range since you are referencing it multiple times as well.

    =COUNTIFS({Class}, OR(@cell = "Webinar: AGM", @cell = "Webinar: Hybrid", @cell = "Webinar: ToolBox", @cell = "Webinar: Objections"), {Date2}, AND(@cell >= [Start Date]1, @cell <= [End Date]1))

    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