Formula Assistance

Options
r0030
r0030 ✭✭✭✭
edited 08/18/22 in Formulas and Functions

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


The problem is that I'm trying to define the criteria above, but what I really need is for the formula to count all cells in my 10 Talent columns if all criteria is met.


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?

Tags:

Answers

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Options

    What about like =countifs({submission type}, Onboarding@row,{Talent 1}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31))) + countifs({submission type}, Onboarding@row,{Talent 2}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31))) etc etc

    Is that what you're looking for?

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Options

    =countifs({submission type}, Onboarding@row,{Talent 1}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31))) + countifs({submission type}, Onboarding@row,{Talent 2}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31))) + countifs({submission type}, Onboarding@row,{Talent 3}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))+ countifs({submission type}, Onboarding@row,{Talent 4}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))+ countifs({submission type}, Onboarding@row,{Talent 5}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))+ countifs({submission type}, Onboarding@row,{Talent 6}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))+ countifs({submission type}, Onboarding@row,{Talent 7}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))+ countifs({submission type}, Onboarding@row,{Talent 8}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))+ countifs({submission type}, Onboarding@row,{Talent 9}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))+ countifs({submission type}, Onboarding@row,{Talent 10}, NOT(ISBLANK(@cell)), AND(@cell DATE(2022, 12, 1), @cell <= DATE(2022, 12, 31)))

  • r0030
    r0030 ✭✭✭✭
    Options

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

    For example, using our formula for the month of June, it 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!