Need help count if formula with multiple criteria.

Options

Need a little help with this one please. I think the blank cell part is stumping me.

So I need to count the # of events an employee managed by event type within a date range (for the whole month) and that have not been cancelled.

I have a column for cancellations. The cell is blank if it was NOT cancelled. If it was cancelled, then in the cell there is the reason why.

Here's an example:

Date Column Name: Event Scheduled

Employee Column Name: Employee

Employee: John

Event Column Name: Event Type

Event: Birthday

Cancellation Column Name: Cancellation

I would like to count how many birthday events John managed in January 2021 that were NOT cancelled(blank). My current formula is counting everything including cancellations.

IMPORTANT: The event type may have multiple selections in the cell. This one has "birthday" and "music".

I want this to be counted and included in my birthday formula and also when I do the same for music formula. I've seen formulas where it will not include it in the count when multiple selections are in the cell.

Many thanks!

Tags:

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    If you have one that is already working with the exception of only pulling in blanks from the cancellation column, the range/criteria set would be

    {Cancellation Column}, @cell = ""


    That's a double set of quotes there after the =.

  • dave619
    dave619 ✭✭✭
    Answer ✓
    Options

    @Paul Newcome Thank you Paul! I think it's working, but I just need to verify and double check the data for accuracy. What is the difference between your formula and using the ISBLANK function?

    And, what would your formula look like if I want it to now count the ones that are NOT blank. Meaning these would be the ones that were cancelled with the reason for cancellation in each cell of the cancellation column.

    Thanks again for your help!

    -D

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!