Help w/formula that needs multiple AND functions

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?

Stefan

Hi @jmoser,

I think the problem is "between".

" [Conflict of Interest Check of EMG & Board (M)]:[Bank Critical Contract - Waiver]" will not work because a formula can only look for the content/status of a cell in a defined column. So every column with checkboxes needs to be addressed individually.

So logic should be

COUNTIFS ([Type of Review]:[Type of Review], "qa file review",

and OR(@cell = "Yes - Defect Remediated", @cell = "No - Fail"), Created:Created, MONTH(@cell) = 7

and CheckboxA:CheckboxA, 1

and CheckboxA:Checkboxb, 1

etc. until you addressed all the checkbox columns.

Hope this helps

Stefan

jmo

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

Thanks for hanging in there with me!

Stefan

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

jmo

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

Stefan

Hi @jmoser,

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

hope it works now.

stefan

jmo

@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

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

jmo

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

Stefan

Hi @jmoser,

I think the problem is "between".

" [Conflict of Interest Check of EMG & Board (M)]:[Bank Critical Contract - Waiver]" will not work because a formula can only look for the content/status of a cell in a defined column. So every column with checkboxes needs to be addressed individually.

So logic should be

COUNTIFS ([Type of Review]:[Type of Review], "qa file review",

and OR(@cell = "Yes - Defect Remediated", @cell = "No - Fail"), Created:Created, MONTH(@cell) = 7

and CheckboxA:CheckboxA, 1

and CheckboxA:Checkboxb, 1

etc. until you addressed all the checkbox columns.

Hope this helps

Stefan

jmo

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

Thanks for hanging in there with me!