Hi Excellers, time for some more #formulafriday fun. This blog post is based on a question I was asked by a subscriber a few weeks ago and I thought it would be a good share it on #formulafriday.
James wanted to know how to count how many times a value (OT1) appeared across a whole workbook, which contained 50 worksheets. He tried to use a 3D reference with the COUNTIF function to carry out the analysis. (A 3D reference allows you to references the cells over multiple worksheets, for example summing the total sales across 12 monthly worksheets) A bit like the formula used in this blog post –
Formula Friday – Easily SUM Values From Different Excel Sheets With The Same Cell Address
The problem is Excel does not support the COUNTIF function in a 3D reference, but that will not stop us!. Let’s take Step 1 to solving this problem.
Step 1. Use The COUNTIF Function On All 50 Worksheets
That’s right, let use the grouping worksheets feature and quickly count how many times OT1 appears on each sheet.
- Select the first sheet in you want included in the analysis
- Hold down the SHIFT key – then click on the last sheet you want included in the analysis
- Your sheets are now ‘grouped’
- Type the formula =COUNTIF(A:A,”OT1″) in cell B2. Column A in each sheet contains the data set we want to count instances of OT1 in.
- Hit Ok. All 50 sheets will now contain this formula and results in cell B2, as we have grouped the sheets before writing the formula.
- ‘Ungroup’ the sheets by selecting any individual sheet.
You now will see if you scroll through your worksheets a count of all instances of OT1. Now it’s time to move into Step 2.
Step 2. Use The SUM Formula To Calculate The Total Numbers Of All Instances Of OT1
We can now use the 3D referencing, by summing all of the formula results of the calculations in Step 1 above.
- Select the cell that will contain your Summary Values, in this case it I added a new worksheet to the excel workbook and call it OT1 COUNT.
- I want the result of how many instances of OT1 in the workbook to be displayed in cell B4 in the worksheet OT1 so in this cell i type =SUM(
- Select the tab of the first sheet we want to include in the analysis in this case it is Sheet1.
- Hold down the SHIFT key and select the last sheet to SUM. In this example it is Sheet50.
- Select the cell to SUM in this case it is B2 on every worksheet
- Close the parentheses on your formula and hit Enter
Your formula will look like this
This formula allows us to find out how many instances of “OT1” are in sheets 1 to 50.
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. If you want to see all of the blog posts in the Macro Mondays Series you can do so by clicking on the link below.
