Help w/multiple column criteria summary report

jmo

Hi team - I've got a sheet with over 100 columns and only specific columns are "mandatory", indicated by an (M) at the end of the column title.

There are several more columns to the right of this graphic - some with a "mandatory" column and some without (meaning there is no "mandatory" helper column for the question for many of the other columns)

I'm trying to figure out the best way to create a summary report that:

• Counts the number of contracts CREATED in a specific month (for example, July), has the check mark in the "mandatory" (M) column and for those mandatory questions (column to the right of the (M) column) the answer is either "N/A" or "No - Fail"

Is there a way to specify then entire column range in a summary report and look for those other criterion to return a number of agreements created in July that have mandatory questions answered either "N/A" or "No - Fail" ?

Thanks for your help on this,

Jeff

David Tutwiler

I think you're looking for a COUNTIF with an OR property. I made an example one that should show you the basics of what you're looking for.

=COUNTIF([Column4]:[Column4], OR(@cell = "N/A", @cell = "No - Fail"))

Here the Summary formula would look at column 4 to see if and of the cells were N/A or No-Fail and start counting them. You would now just need to add the section about checking to make sure the month was July and then adding the rest of your mandatory columns.

Was this what you were looking for?

jmo

Hi @David Tutwiler - truly appreciate the feedback.

Do you happen to know if there's a way to do a series of columns to look across? Something like =COUNTIFS([Column 1]:[Column 100], OR(@cell = "N/A", @cell = "No - Fail")) ???

Column 1 & 100 would be the start/end of all of the columns I need to look between. For "true" in the "mandatory" columns and N/A or No - Fail in the other columns?

David Tutwiler

I think you would just do [Column 1]:[Column 100] as your range as you have written.

Paul Newcome

Are you looking across the entire row or do you need to count potentially multiple instances within a single row?

jmo

Hi @Paul Newcome - I'm looking across the row but only between specific columns. I have a bunch of "admin" columns upfront and then questions that need specific drop down answers, check boxes & comments between the remaining columns. It's in those remaining columns that I need to look within for the "N/A" or "No - Fail" text.

Does that make sense?

Paul Newcome

It does. How many of these checkbox columns do you need to count across the row?

jmo

There are 20 columns with check boxes that identify a column to the right of that box as a "mandatory" question that needs to be answered.

Paul Newcome

Are there any columns that could possibly have either "N/A" or "No - Fail" in them that you are going to exclude from the count?

jmo

Yes there would be other columns that have N/A or No - Fail that would need to be excluded from the count but shouldn't be counted if a "mandatory" column is not check marked

Paul Newcome

Ok. I am going to recommend a "helper column where you get the count for each row first then use a SUM on the helper column.

=IF(OR([First Column]@row = "N/A", [First Column]@row = "No - Fail"), 1) + IF(OR([Second Column]@row = "N/A", [Second Column]@row = "No - Fail"), 1) + IF(OR([Third Column]@row = "N/A", [Third Column]@row = "No - Fail"), 1) + ................................................................

The above would continue until you entered each of the mandatory columns. Then you would run the SUM on this helper column.

jmo

Hi @Paul Newcome - I'm still at it.

I've now created multiple helper columns that indicate whether a "mandatory" question has either N/A or No - Fail, example for the columns below: =IF(OR([Conflict of Interest Check of EMG & Board]@row = "Yes - Defect Remediated", [Conflict of Interest Check of EMG & Board]@row = "No - Fail"), 1)

I did that across all 20 of the "mandatory" questions @row.

What I'm struggling with now is how to add agreements created in July plus mandatory questions that contain either N/A or No Fail @row?

I tried a super long =COUNTIFS formula but it's resolving to 0 - but it does error out when I add Created:Created, MONTH(@cell) = 7):

=COUNTIFS([Conflict of Interest Check of EMG & Board (M)]@row, 1, [Conflict of Interest Check of EMG & Board (helper)]@row, 1, [E&Y Accounting Firm (M)]@row, 1, [E&Y Accounting Firm (helper)]@row, 1, [SLA/SLO (RFx) (M)]@row, 1, [SLA/SLO (RFx) (helper)]@row, 1, [Certificate of Insurance (COI) (M)]@row, 1, [Certificate of Insurance (COI) (helper)]@row, 1, [Background Checks (M)]@row, 1, [Background Checks (helper)]@row, 1, [Standard Contract Language (M)]@row, 1, [Standard Contract Language (helper)]@row, 1, [SOC Reporting Requirements (M)]@row, 1, [SOC Reporting Requirements (helper)]@row, 1, [PCI/Card Holder Data (M)]@row, 1, [PCI/Card Holder Data (helper)]@row, 1, [Commercial Evaluation (M)]@row, 1, [Commercial Evaluation (helper)]@row, 1, [Scope & Nature of Agreement (M)]@row, 1, [Scope & Nature of Agreement (helper)]@row, 1, [SLA/SLO (All) (M)]@row, 1, [SLA/SLO (All) (helper)]@row, 1, [Pricing Evaluation (M)]@row, 1, [Pricing Evaluation (helper)]@row, 1, [Contract Gap Analysis (M)]@row, 1, [Contract Gap Analysis (helper)]@row, 1, [Source Selection Justification (M)]@row, 1, [Source Selection Justification (helper)]@row, 1, [Approval Authority Delegated (M)]@row, 1, [Approval Authority Delegated (helper)]@row, 1, [Legal Triggers (M)]@row, 1, [Legal Triggers (helper)]@row, 1, [Proper Authority to Execute (M)]@row, 1, [Proper Authority to Execute (helper)]@row, 1, [Brand/Logo Use (M)]@row, 1, [Brand/Logo Use (helper)]@row, 1, [Bank Critical Contract (M)]@row, 1, [Bank Critical Contract (helper)]@row, 1, [Bank Critical Contract - Waiver (M)]@row, 1, [Bank Critical Contract – Waiver (helper)]@row, 1)

Any help creating a summary or other formula to add these up?

Paul Newcome

I'm not sure what you are trying to accomplish with your COUNTIFS. Are you trying to get the count for that single row of how many are checked?

jmo

Hi @Paul Newcome - let me see if I can capture succinctly what I'm trying to count.

I need to sum up, by month (this example, July), the total number of agreements (rows) that have mandatory columns (specific columns w/check boxes), with column entries/answers that either contain N/A or No - Fail.

I created "helper" columns for contains N/A or No - Fail (actually what I'm looking for is Yes - Remediated or No - Fail)

Helper column formula (example): =IF(OR([Conflict of Interest Check of EMG & Board]@row = "Yes - Defect Remediated", [Conflict of Interest Check of EMG & Board]@row = "No - Fail"), 1); I use that across all 20 of my mandatory question columns

So I need a total, by month, on agreements that have mandatory questions that either say Yes - Defect Remediated or No - Fail.

Note: sorry about using N/A first, I truncated the string thinking this thread would be much easier to answer. :-)

Here is a snapshot of my columns - up front & the start of the "mandatory" questions:

The other "mandatory" questions have the same formatted columns:

• (M) check box - indicates whether this question is mandatory or not
• (helper) check box - looks to the column to the right and looks for Yes - Remediated or No - Fail (=IF(OR([Conflict of Interest Check of EMG & Board]@row = "Yes - Defect Remediated", [Conflict of Interest Check of EMG & Board]@row = "No - Fail"), 1)

That explanation isn't as short as I thought it'd be. :-)

Paul Newcome

Ok. What are the possibilities in the [Conflict of Interest Check of EMG & Board] column? If it is just yes, no, or n/a, then instead of saying "IF it equals "yes" OR "no"" we could shorten it by saying "If it does not equal "n/a"".

Either way... You actually don't need a helper column for each of the mandatory sets. You just need one helper column for the whole sheet that uses a series of IF statements that each generate a 1 for each set that meets your criteria and get added together.

=IF([1st Column]@row <> "N/A", 1) + IF([2nd Column]@row <> "N/A", 1) + ..............

Using the above you would have a 1 generated and added together for each column that meets the criteria. So if the first column doesn't equal n/a and the second column does (or the other way around) then the formula would output 1, but if both did not equal n/a then the formula would output 2.

This would go into a single helper column on every row.

Then to get the counts by month, you would use a SUMIFS to sum this single helper column and base it off of the dates.

jmo

Hi @Paul Newcome - the options for all of the questions (mandatory or not) are:

• Yes - Defect Remediated
• Yes - Defect Rebutted
• No - Fail
• N/A
• Yes - Pass

So I can't seem to come up with an appropriate "does not equal" since I either need 2 options with an OR statement or "does not equal" with 3 options.

So either does not equal Yes - Defect Rebutted, N/A or Yes - Pass

or

Is equal to Yes - Defect Remediated or No - Fail

Any idea how to go about that?