Help w/formula that needs multiple AND functions

Options
jmo
jmo ✭✭✭✭✭✭

Hi team - I have a sheet w/over 100 columns and I'm trying to create a summary report that:

  • Counts agreements created in July AND
  • Is a "mandatory" column (check box) AND
  • Is between the selected columns (actual range will be [Conflict of Interest Check of EMG & Board (M)]:[Conflict of Interest Check of EMG & Board (M)]) AND is EITHER "Yes - Defect Remediated" or No - Fail"

I've tried the following and get 0 but it really should be at least 2 (was just checking for Yes - Defect Remediated):

=COUNTIFS([Type of Review]:[Type of Review], "qa file review", [Conflict of Interest Check of EMG & Board (M)]:[Conflict of Interest Check of EMG & Board (M)], 1, [Conflict of Interest Check of EMG & Board (M)]:[Conflict of Interest Check of EMG & Board (M)], "Yes - Defect Remediated", Created:Created, IFERROR(MONTH(@cell), 0) = 7)


Any ideas how I can combine those functions?

Best Answers

  • jmo
    jmo ✭✭✭✭✭✭
    Answer ✓
    Options

    Yikes @Stefan - that's a lot of formulas.

    Thanks for hanging in there with me!

Answers

  • Stefan
    Stefan ✭✭✭✭✭✭
    Options

    Hi @jmoser,

    does this work?

    =COUNTIFS([Type of Review]:[Type of Review], "qa file review", [Conflict of Interest Check of EMG & Board (M)]:[Conflict of Interest Check of EMG & Board (M)], 1, [Conflict of Interest Check of EMG & Board (M)]:[Conflict of Interest Check of EMG & Board (M)], OR(@cell = "Yes - Defect Remediated"; @cell = "No - Fail"), Created:Created, MONTH(@cell)= 7)


    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • jmo
    jmo ✭✭✭✭✭✭
    Options

    Hi @Stefan - I got an unparseable error when I copy/paste your formula.

  • Stefan
    Stefan ✭✭✭✭✭✭
    Options

    Hi @jmoser,

    sorry, there is a ; that needs to be replaced with a ,

    hope it works now.

    stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • jmo
    jmo ✭✭✭✭✭✭
    Options

    @Stefan - when I take out the semicolon (after "Yes - Defect Remediated" and before @cell) in the formula, it's no longer UNPARSEABLE but results in 0


    =COUNTIFS([Type of Review]:[Type of Review], "qa file review", [Conflict of Interest Check of EMG & Board (M)]:[Conflict of Interest Check of EMG & Board (M)], 1, [Conflict of Interest Check of EMG & Board (M)]:[Conflict of Interest Check of EMG & Board (M)], OR(@cell = "Yes - Defect Remediated", @cell = "No - Fail"), Created:Created, MONTH(@cell) = 7)

    Should the semicolon be there and my problem with UNPARSEABLE is elsewhere?

  • Stefan
    Stefan ✭✭✭✭✭✭
    Options

    Hi @jmoser,

    ok, unparseable gone.

    Are you 100%, that all the column titles and status are exactly correct?

    I ask because in your screenshot I cannot see column titles like „Conflict of Interest ...“ but „Certificate of interest...“.

    hope you figure it out

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • jmo
    jmo ✭✭✭✭✭✭
    Options

    Ugh - ok @Stefan, so I tried to place the full range of the columns I need checking but am now getting an INCORRECT ARGUMENT SET error:

    =COUNTIFS([Type of Review]:[Type of Review], "qa file review", [Conflict of Interest Check of EMG & Board (M)]:[Bank Critical Contract - Waiver], 1, [Conflict of Interest Check of EMG & Board (M)]:[Bank Critical Contract - Waiver], OR(@cell = "Yes - Defect Remediated", @cell = "No - Fail"), Created:Created, MONTH(@cell) = 7)

    The range should run from (start) Conflict of Interest Check of EMG & Board (M) and run through the Bank Critical Contract - Waiver (end) columns.

    Still looking to get an end result that counts:

    • Agreements that were created in July AND
    • Are marked as "mandatory" (check box) anywhere between Conflict of Interest Check of EMG & Board (M) & the Bank Critical Contract - Waiver columns AND
    • Either have Yes - Defect Remediated OR No - Fail between Conflict of Interest Check of EMG & Board (M) & the Bank Critical Contract - Waiver columns


  • jmo
    jmo ✭✭✭✭✭✭
    Answer ✓
    Options

    Yikes @Stefan - that's a lot of formulas.

    Thanks for hanging in there with me!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!