Formula Assistance

Options

I'm struggling to pull a particular formula from my data source sheet into my metrics sheet.


My criteria is to Count If:

  • Submission Request is "Manager Change (Talent Only)


  • Talent columns #1-10 are not blank


  • Submitted Date is between August 1 and August 31


I've tried several things. The last effort being the below formula:

=COUNTIFS({Submission Type}, Classification@row, {Talent 1}, NOT(ISBLANK(@cell)), {Talent 2}, NOT(ISBLANK(@cell)), {Talent 3}, NOT(ISBLANK(@cell)), {Talent 4}, NOT(ISBLANK(@cell)), {Talent 5}, NOT(ISBLANK(@cell)), {Talent 6}, NOT(ISBLANK(@cell)), {Date Submitted}, AND(@cell >= DATE(2022, 12, 1), @cell <= DATE(2022, 11, 31)))


However, everything I've tried is returning either an error message or the number 0 - neither of what I want. Could anybody provide assistance? Thank you in advance!

Tags:

Best Answer

Answers

  • MedaUser
    MedaUser ✭✭✭✭
    Answer ✓
    Options

    I personally don't have much luck with the ISBLANK formula. Have you tried replacing those with the following?

    =NOT(@cell="")
    
  • r0030
    r0030 ✭✭✭✭
    Options

    I hadn't thought to try that, but it seems like it's working now! Thank you so much!

  • MedaUser
    MedaUser ✭✭✭✭
    Options

    Great! Happy to help!

  • r0030
    r0030 ✭✭✭✭
    Options

    @MedaUser Circling back to this and making it a little more complicated ;)

    Is there a way to achieve the same things above, but instead of just counting if the items meet the criteria, I need to count the number of "Talent" that are associated with each line. Below is an example of how my sheet is set up for the Talent Fields:


    When I enter this formula -

    =COUNTIFS({Submission Type}, Onboarding@row, {Talent 1}, NOT(ISBLANK(@cell)), {Talent 2}, NOT(ISBLANK(@cell)), {Talent 3}, NOT(ISBLANK(@cell)), {Talent 4}, NOT(ISBLANK(@cell)), {Talent 5}, NOT(ISBLANK(@cell)), {Talent 6}, NOT(ISBLANK(@cell)), {Talent 7}, NOT(ISBLANK(@cell)), {Talent 8}, NOT(ISBLANK(@cell)), {Talent 9}, NOT(ISBLANK(@cell)), {Talent 10}, NOT(ISBLANK(@cell)), {Date Submitted}, AND(@cell >= DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))

    -it populates 3 line items, which is correct. However, I want it to count all cells within Talent #1-#10 if the criteria is met.


    Does that make sense?

  • MedaUser
    MedaUser ✭✭✭✭
    Options

    I think this makes sense. Are you getting an error right now or is it counting inaccurately?

    If error, then I would suggest replacing the NOT(ISBLANK(@cell)) with what I suggested above NOT(@cell ="").

    If counting inaccurately, then I'll need to understand if the number populating for you is lower or higher than you are expecting.

  • r0030
    r0030 ✭✭✭✭
    Options

    Currently it's not generating an error, but pulling inaccurately. I replaced NOT(ISBLANK(@cell)) with NOT(@cell="") but it is unfortunately giving the same result. Below is the formula I used:


    =COUNTIFS({Submission Type}, Classification@row, {Talent 1}, NOT(@cell = ""), {Talent 2}, NOT(@cell = ""), {Talent 3}, NOT(@cell = ""), {Talent 4}, NOT(@cell = ""), {Talent 5}, NOT(@cell = ""), {Talent 6}, NOT(@cell = ""), {Talent 7}, NOT(@cell = ""), {Talent 8}, NOT(@cell = ""), {Talent 9}, NOT(@cell = ""), {Talent 10}, NOT(@cell = ""), {Date Submitted}, AND(@cell >= DATE(2022, 6, 1), @cell <= DATE(2022, 6, 31)))


    I think it is pulling in the correct number for what I'm telling it to return. I think it should be maybe a collect or something, but not exactly sure.


    For example, the number returned using the above formula for the month of June returns the value 3. This is technically correct in terms of the number of line items that meet the criteria. The problem is that the number should be 30, because I want it to count every cell within the Talent # 1 - Talent # 10 columns if criteria is met.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!