Premise

A few months back, a friend contacted me with an interesting Excel question. They were responsible for maintaining qualitative approaches to the physical maintenance of equipment used by university students. They were asked to develop reports of each equipment box that was lent out and provide quantitative feedback for their qualitative metrics. They had set everything up, but they were unable to navigate a few of the next steps.

They were interested in how they could count the number of ‘Yes’es or ‘No’s across multiple sheets in a workbook. Essentially, ‘can you use a mixture of COUNTIF and INDIRECT to dynamically count across ‘n’ number of worksheets?’

What an interesting question and it was a question that enabled my brain to ask several more. I attempt to keep as much information as I can on one sheet and only reference other sheets in very specific ways if I can. I hadn’t considered playing around with INDIRECT before, and it opened up a different lens for me to view Excel/Google Sheets in. Yes, this is just a slightly different way to produce a relative data type, but I hadn’t actually thought about digging into it.

Mock Sample

First thing I need to point out is that I attempted to create this process twice in Google Sheets, but I am unable to configure a collection of functions to behave in the same way that Excel allows them. Specifically, an array within INDIRECT and COUNTIF. There are some methods that are straight forward in Excel and not in Google Sheets, and the reciprocal also stands. Since my friend’s job holds confidential information, I have decided to use a completely different framework, but it still conveys the same types of calculations.

For this different framework, I considered jobs that required checklists, and outside of ISO quality control, I landed on workplace health & safety. Specifically, electrical safety. I have taken the following checklist .pdf:

https://www.worksafe.act.gov.au/health-and-safety-portal/safety-resources/safety-checklists

And converted it to an Excel document. Keep in mind — you will not see the full functionality of this spreadsheet if you open it in Google Sheets. Make sure you download the file and open it in Excel:

https://drive.google.com/drive/folders/1VXhSvPVZEaStE_45ELwIUULkM0US2CNT

For your information, this is the outline of the blog:

• Setting up the Reports
• Conditional Formatting
• Create the Master Sheet
• Main Questions Count
• Creating Additional Reports
• Automatically Accounting for Reports
• Automate the List of Report Names
• Checkpoints
• Formatting

Setting up the Reports

Now that we have the individual checklist report, we can start to check for each inspection.

We can see that this first check has yielded a positive result:

Conditional Formatting

Let’s make the formatting for the marker easier though by adding conditional formatting for the question portions. I’ll select the first Y text cell (C18), then click Home > Conditional Formatting > Manage Rules:

I’ll select Show formatting rules for: > This Worksheet, and then click the ‘+’ on the bottom left of the window:

Next, change the formatting rule to Classic:

Then select Use a formula to determine which cells to format:

I’ll enter the following formula into the formula box and select Green Fill with Dark Green Text:

 =IF(C18=”Y”,1,0)

I will then select all of the cells that I want this rule to apply to:

Next, I’ll do the same for the No answers (Column D):

I’m also going to add a 10 checkpoint list of markers that auditors would follow during their work:

For each of these Checkpoints, I’ll create a series of conditional formatting that is triggered by one of three words: Good, Adequate, and Unsatisfactory. I’ll complete this using Data Validation — select all of the cells (H19:H28) > navigate to the Data tab > click Data Validation:

Under Allow, select List:

We will enter the three classifications; Good, Adequate, Unsatisfactory:

Now we have these three options to select in those cells:

I’ll add conditional formatting to these cells or each of the three classifications: Good will display a green cell, Adequate will display an orange cell, and Unsatisfactory will display a red cell:

I’ll clean everything up by formatting the Report sheet a bit:

Last thing that I’m going to do with this report is name the sheet accordingly. This will be a part of the main functioning of this process. I’m going to make things a little simpler on myself (and you) by not including a space between the sheet name and sheet index (number); Report1:

Create the Master Sheet

The next step is to create a sheet that accounts for all of the subsequent reports that will be created. I’ll create a new sheet and name it Master Report:

The main bits of data that I’m interested in are the 10 considered questions and the 10 checkpoints. I’m also going to add a % column next to Y and N, and expand the checkpoint classifications out:

Main Questions Count

The thing that I’m interested in monitoring for these checks are the macro number of businesses who are adhering to the necessary legal requirements during the audits. I’m going to start by checking what the value of D18 in Report1 is:

=INDIRECT(“Report1!D18”)

This shows me that there is a ‘Y’ in this cell. I’ll now add a COUNTIF function outside of the INDIRECT function so that it starts to add them up; I’ll also add a little shortcut for myself and use the ‘Y’ in the cell above on the Master Report sheet:

=COUNTIF(INDIRECT(“Report1!D18”),D$5)

I can now copy this formula across to E6 and count how many N’s are in the adjacent cell:

Looking ahead, I’ll want to track what percentage of questions are being passed. This was why I added this in column F in the initial creation:

=D6/(D6+E6)

If I think ahead, for the subsequent reports that are going to be created (and accounted for), I’d be needing to manually add each report for x number of potential future reports. Since I’m a fan of dynamism, let’s see if we can implement a solution that automatically accounts for this. First, let’s create a space for maintaining the name of reports that will be created:

Since the formula is looking for cells in Report# ($N$6:$N300), searching in column D, but with changing rows, I’ll integrate the ROW() function to search the specific row number, meaning that I can now copy/drag this formula around and it still be an accurate cell lookup. I’m also going to account for Y’s and N’s by using relative column referencing (D) and absolute row referencing ($5):

=SUMPRODUCT(IF($N$6:$N300<>””,COUNTIF(INDIRECT(“‘”&$N$6:$N300&”‘!D”&ROW(D18)), D$5), 0))

The last thing that I want to account for is the ‘!D’ (column) portion of the INDIRECT function and I will accomplish this by using a combination of the COLUMN and VLOOKUP functions. COLUMN will return the column number (not letter) of any cell that you reference. Let’s say I want to see what column number cell D5 is. Since A is column 1, B is column 2, and C is column 3, if I enter COLUMN(D5) into Excel, it will return a 4:

Since we know that we are going to get number values, we can use the VLOOKUP function  to assist with the solution. I’ll create a lookup table in the Master Report sheet, name it Lookup (using the name range), and colour the text white so that it’s not visible to users:

We can use a VLOOKUP function to translate the COLUMN function into a letter. We already know that we’re using this to search within the Report1 sheet and referencing particular cells. This is where the VLOOKUP and COLUMN functions are here to help us with automation. We are using the COLUMN function to tell us the number column we’re on. We use that number output to lookup the corresponding number in the LOOKUP sheet by using the VLOOKUP function. This is transforming the number into a letter. We use this letter within the INDIRECT function to locate the appropriate column in the Report1 sheet.

After adding this VLOOKUP function to the formula, this is now outputting the correct value for all reports created, Report1. I’ll also be sure to copy this formula over to E6 and F6:

=SUMPRODUCT(IF($N$6:$N300<>””,COUNTIF(INDIRECT(“‘”&$N$6:$N300&”‘!”&VLOOKUP(COLUMN(D$5),Lookup,2,0)&ROW(D18)), D$5), 0))

Creating Additional Reports

To make things a little easier on myself and make sure that the reports follow a naming convention that can be predicted and easier to account for. So, I will rename Report1 to Report (1) so that subsequent reports will follow this (#) structure. However, the user will need to delete any data that is copied over from each previous report:

Delete all of the data:

Automatically Accounting for Reports

Now, let’s give this automation COUNTIF formula a whirl. Since I have Report (2), I’ll add some data into it — I’ll then add in Ys to all of the 10 questions:

Navigating back to the ‘Master Report’ sheet, we should still be seeing a 1 in the first question:

When we add Report (2) under the Reports section, this will now account for the newly created sheet:

Since this is working how we are expecting, we can now copy this formula and apply it to all of the questions in this section.

Automate the List of Report Names

Instead of having to write all of these report names manually, let’s get Excel to account for them automatically. Under the Ribbon tab, select Formulas > Name Manager:

Next, click New:

Then under Name write ListNames, and under Refers to enter:

=REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)

I can now replace the ‘Reports’ list (column N) with the list of sheet names (located in the bottom of Excel. In Cell N5, I’ll enter the following formula:

=IFERROR(INDEX(ListNames,SEQUENCE(296)),””)

Checkpoints

Lastly, let’s not forget about the checkpoints along the way. We’re going to follow the same practice that we did for the 10 questions:

=SUMPRODUCT(IF($N$6:$N301<>””,COUNTIF(INDIRECT(“‘”&$N$6:$N301&”‘!”&VLOOKUP(COLUMN($I$6),Lookup,2,0)&ROW(I19)), J$6), 0))

I can now copy this formula across the columns and down the rows:

And we can see the formula accurately accounting for all the different categories in the arrays:

Formatting

To clean things up, I’ll remove the gridlines on all sheets, implement conditional formatting on the Master Report, and finalise all official information:

Conclusion

In this guide, we’ve delved into the intricacies of counting specific values across multiple spreadsheets using Excel’s powerful functions. The challenge posed by my friend about counting ‘Yes’ or ‘No’ responses across various sheets opened up a valuable exploration into using COUNTIF with INDIRECT to dynamically aggregate data. While the approach required a mixture of strategies and a touch of creativity, it highlighted Excel’s capability to handle complex data management tasks efficiently.

By transitioning from a qualitative framework to a quantitative one, such as the electrical safety checklist, we could maintain the integrity of the process while demonstrating its versatility. Setting up conditional formatting enhanced the readability and user experience, making the data interpretation straightforward.

Creating a master sheet to compile results from individual reports and automating the process with dynamic formulas ensured a scalable solution for ongoing and future audits. Automating the list of report names further streamlined the workflow, reducing manual input and potential errors.

Through this exercise, not only did we solve the initial problem, but we also discovered a broader application for these techniques. Excel’s combination of INDIRECT, COUNTIF, and SUMPRODUCT, provides a robust toolkit for any data analyst looking to optimise their workflow across multiple sheets.