Help w/formula that needs multiple AND functions
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
-
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
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Yikes @Stefan - that's a lot of formulas.
Thanks for hanging in there with me!
Answers
-
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.
-
Hi @Stefan - I got an unparseable error when I copy/paste your formula.
-
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
@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?
-
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.
-
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
-
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
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
Yikes @Stefan - that's a lot of formulas.
Thanks for hanging in there with me!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!