Multiple Criteria, total count

Options
✭✭✭✭

Hi All,

I am trying to do calculate a total number of events, that match criteria for 1. Date range (can also use fiscal column as a matching criteria where i only want those that contain specific letter/numerical value = Q2) for all -NDA Webinars. I don't want it to count each column that matches the criteria, but if the two criteria match, i want it to count as 1 event. Does that make sense?

I have tried countifs and sumifs, and cannot seem to make it work. Can someone suggest a formula?

Tags:

• ✭✭✭✭✭✭
Options

Using your screenshot above, what would be the expected result/count of the formula?

• ✭✭✭✭
Options

Ha! Bad example, but in this screen shot the number would be 0. Here's another screenshot to show where total for Q2 NDA Webinars should result in 9 count.

• ✭✭✭✭✭✭
Options

Haha. That explains why I was a little confused.

Try something like this (I am also assuming you want to account for FY21)...

=COUNTIFS([Even Form Factor]:[Event Form Factor], "NDA Webinar", Fiscal:Fiscal, CONTAINS("FY21Q2", @cell))

• ✭✭✭✭
Options

Hi Paul,

Tried this, but it comes back as no match. Im wondering if that is because this column is coded to return the FYQ1Wk based on matching to start date aka it's a vlookup. Could that be why?

Some of the other forums seemed like it could not work based on date. I tried this below, and it didn't work either. Thoughts?

=COUNTIFS([Event Form Factor]:[Event Form Factor], "NDA Webinar", Start Date: Start Date, DATE(2020,07, 31) => DATE(2020, 05, 04))

• ✭✭✭✭✭✭
Options

That's odd. It shouldn't matter the source data. The CONTAINS function is looking for the "FY21Q2" string located anywhere within the cell. I do something very similar except I use INDEX/MATCH instead of VLOOKUP.

Check your Fiscal Column and see if any of those cells have the #NO MATCH error in it. That's the only reason I can think of that a COUNTIFS would generate that error. If it found no rows that matched the criteria to count, then it should be generating a 0 (zero) instead of the error.

• ✭✭✭✭
edited 05/28/20
Options

Hi Paul,

Tried it again today with fresh eyes, and it still came back "no match", i thought it was because it's a multi-select column, but it works for the simple countifs. I'll think about how else i can get the outcome i need in a different way.

• ✭✭✭✭
edited 05/28/20
Options

Hi Paul,

Tried it again today with fresh eyes, and it still came back "no match"

Kept re-typing and trying again and this worked! IDK what i really changed, but super excited :)

=COUNTIFS([Event Form Factor]:[Event Form Factor], "NDA Webinar", [Start Date]:[Start Date], DATE(2020, 5, 4) <= DATE(2020, 7, 31))

• ✭✭✭✭✭✭
Options

Glad you were able to get something working. 👍️

• Options

I am trying to do a similar function.

I am trying to count if [Project Type] Contains "Implementation + VI", and "Implementation - Video Intelligence"

• ✭✭✭✭✭✭
Options

=COUNTIFS([Project Type]:[Project Type], OR(@cell = "Implementation + VI", @cell = "Implementation - Video Intelligence"))

• Options

That worked very nicely!

• ✭✭✭✭✭✭
Options

Great! Happy to help! 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!