Crosssheet Countifs using Date and OR

Karina.Perez

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.

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)

Shaine Greenwood

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

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.

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))