# Help w/formula that needs multiple AND functions

Options
✭✭✭✭✭✭

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?

• ✭✭✭✭✭✭
Options

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

Thanks for hanging in there with me!

• ✭✭✭✭✭✭
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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
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.

• ✭✭✭✭✭✭
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?

• ✭✭✭✭✭✭
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.

• ✭✭✭✭✭✭
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

• ✭✭✭✭✭✭