Multiple Criteria, total count

Mona Singh

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?

Paul Newcome

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

Mona Singh

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.

Paul Newcome

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

Mona Singh

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

Paul Newcome

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.

Mona Singh

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.

Mona Singh

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

Paul Newcome

Glad you were able to get something working. 👍️

bradleyesmith86431

I am trying to do a similar function.

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

Paul Newcome

@bradleyesmith86431 Try something like this...

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

bradleyesmith86431

That worked very nicely!

Paul Newcome

Great! Happy to help! 👍️