Count formula returning specific information

Hi,

I'm working with one sheet doing a count of any openings I have in my courses. I'm trying to create a quick visual on my dashboard, but realize I must first create the formula. So I have a sheet called Counts and it is currently set up to pull back the data for Open TA slots, Cap, and Filled columns.

I would like to have the Fall or Spring column fill in with the # that matches based on my Active TAships Sheet.

I have to adhere to strict budgets, so if there is a cap of 3 for a specific course, I need to be sure there's a hard stop in place to prevent oversubscribing.

Any thoughts? Thanks so much!

Lori

Answers

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭

    Hi @maineL

    I think I follow you here but just in case...what value are you looking for in the top row / fall column? Is it the number of times "F" appears in the Term Column when the Course in green matches the Course in Red? If so, try this formula and let me know how it goes.

    =COUNTIFS({Term}, "F", {Course}, Course@row)

    The {Term} and {Course} are cross sheet references to the source sheet. Good luck!

    Thanks,

    Matt

    Matt Johnson

    Sevan Technology

    Smartsheet Aligned Partner

  • maineL
    maineL ✭✭

    Hi Matt,

    In essence, yes, I'd like the number of times F appears for a specific course and have the respective Fall or Spring column populate. So I took your formula and updated it to match the sheet names:

    =COUNTIFS(Active TAships [Term], "F", (Active TAships [Course] Course@row))

    But now it's just saying Unparseable. Any idea how to fix it?

    Thanks so much!

    Lori

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!