- Author of the publication:mrempel
- Post published:6 January 2020
- Post Category:Excel Tips, Tricks and Training / INDIRECTLY
- Write a comment:22 comments
In this tutorial, you'll learn how to count the number of times an item appears in a workbook across multiple Excel spreadsheets.
Candownload the file hereand continue. When you preview, look for Download in the top right corner.
I learned this process from Dave Bruns of ExcelJet. You will find the link to your explanation.Here.
I have a workbook with 4 worksheets: 1, 2, 3, and 4, plus a summary sheet. I want to count the number of times the word "help" appears in all the worksheets. Note that I wrote a simple oneCONTINUE IFFormula in cell A12 across all worksheets to count the number of times each worksheet contains the word "help". Here are the four worksheets:
If you add theseCONTINUE IFIf you enter formulas manually, you will see that they contain the word "help" a total of 36 times. To test this, I created a simple formula on the summary sheet to summarize them:
However, this led to various formulas being written to achieve this. What if I want to do this in a formula?
The first thing we need to do is create a named range from the list of all the names in our worksheets. I created the list in the summary worksheet in cells A4:A7 and you can see in the name box that it says "Sheets". We use this named range in our formula.
Notice that I typed the word "help" in cell A9. This will be a referenced cell in ours.CONTINUE IFPart of our formula, as you will see later.
The functions we will use are:
Here is our formula:
How does it work? Let's break it down from the inside out.
oINDIRECTLYThe function takes the text and turns it into a reference that Excel can use in that formula. I concatenated multiple elements using the ampersand ("&") resulting in the ranges used inCONTINUE IFCharacteristic. ISLANDINDIRECTLYFunctional section of it is:
INDIRECT(" ' "&Spreadsheets&" ' !"&"A1:E10")
NOTE: I've separated some of the components with spaces to make it easier for you to see what's included, but you can't use those spaces in your formula.
Each of the concatenated items is actually text, so they must be enclosed in double quotes (" ").
If I highlight this section of the formula and press F9, you can see the result:
Excel took these concatenated items and turned them into ranges for each of the worksheets I've listed in my named range.
Now himCONTINUE IFThe function can use these ranges with the criteria in cell A9 ("Help") to count the number of occurrences of that word on each worksheet. Again, selecting that section of the formula and pressing F9 produces the following result:
Now himSUMPRODUCTThe function adds these values and returns the final result of 36.
What can you do next?
Share this post with others who could benefit!
Leave a comment or reply below. Let me know what you think!
Subscribe to this blog for more great tips in the future or...
Take a look at my Youtube channel!
You may also like
Format multiple worksheets with fill in sheets in Excel
How to find the most common text element in Excel
How to use the ADDRESS function in Excel
This post has 22 comments.
sessionApril 21, 2020respondedor
Thanks for this example. I'm looking for a slightly different app and maybe you have an easy way to get the result. If you can help it would be greatly appreciated. Health.
I have employee lists broken down on separate sheets with various other bits of data and I want to create something like a pivot table so I can split the data for different uses.
Name/User ID/Location/Mobile Device/Laptop
Total by division
Total by location
Michael Rempl April 21, 2020respondedor
Depending on how the data is organized, define each data region as a table and use relationships to join them in a PivotTable. If you want you can send me a copy of the file and I'll take a look at it. Send firstname.lastname@example.org a detailed explanation of what the result should look like. This may help:https://www.excel-bytes.com/relaciones-en-tablas-dinámicas-en-excel/
zoe sanchez3. November 2020respondedor
I wanted to ask if you could help me with a formula. I have an online business and want to track how many are sold through our SKU system. I have 12 sheets for the different months. I counted but only allowed three arguments. What I want to do is figure out how many were sold this month for each item we have and put that on our master spreadsheet. If the same item is sold the following month, I want the master spreadsheet to be able to add the items sold that month to the total that was already there the previous month. Can you help me with that?
mrempel3. November 2020respondedor
Zoe, I'm happy to help. You can send me a copy of your workbook with an explanation and I will find a solution for you. Send email@example.com.
jamie4. November 2020respondedor
I created an order form to make it easier to split the coding into the order. When I send the order to the company, they don't want the order form, so I have to put everything I ordered and the amount I have to send them. I don't always sort everything listed so I'm trying to find a way to highlight every item with a quantity greater than 0 on 4 different pages and have them form a list that I can put in an email to tell them to ship the company instead of spending all the extra time re-entering the order.(Video) How To Count Items Across Multiple Worksheets In Excel
mrempel4. November 2020respondedor
Jamie, I don't know of an easy way to do this, but if you're willing to tweak the tuning a bit, it could be pretty quick in the long run. Can you convert your data on all 4 pages into spreadsheets first? In this case, you can use Power Query (or find and transform data) to add it to a table. From there you can use the filter function (Office 365 version) or my process to extract a dynamic list (https://excel-bytes.com/wie-man-eine-dynamische-liste-aus-einem-datenbereich-basierend-auf-einem-kriterium-ohne-filter-in-excel-extrahiert/) to drag only the items with a certain amount to another worksheet. I can help you with that if you want. You can email me firstname.lastname@example.org discuss further.
André25 November 2020respondedor
Thanks for the tutorial on this. It was helpful. Is it possible to go a step further and include a date range?
Each sheet has a column with the date something was completed... Column R
The criteria on each sheet that need to be counted are column T
In a new "summary sheet" the calculations would be done with "SUMPRODUCT(CONT.IF(INDIRECT)".
In this "Summary" sheet, column "A" contains data representing a week in which the specific criteria occurred.
Columns row 1 in columns B, C, and D contain the criteria that will be counted based on the week listed in column A.
Unfortunately limited in forum format
to your arm
Line 1 week from
Line 2 11.15.2020
Line 3 11/22/2020
Line 4 11/29/2020
Line 1 "Lost"
line 2 5
Line 3 12
Line 4 11
Line 1 "Caught"
line 2 7
Line 3 11
Line 4 13
Line 1 "Disconnect"
Line 2 11
line 3 1
Carrera 4 5
I hope it makes sense. I couldn't get anything to work.
Thanks in advance.
mrempel25 November 2020respondedor
Andrew, is there any chance you could send me a copy of your workbook with an explanation so I have something to work with? Send email@example.com
donald barr 15 January 2021respondedor
That's great, but I wonder if I could go further: what I have are attendance sheets for different subjects, each on a different spreadsheet. Each worksheet has the same student names in the first column and dates in the top row. The formula on my call summary page is
=SUMPRODUCT(CONT.IF(INDIRECT("'"&$B$93:$B$104&"'!"&"G5"),"P"))(Video) Using the COUNTIF function across sheets in a workbook
which works perfectly for the first student on the first day, but what I need to do now is copy and paste this for all students/data on the overview page. As it stands, the G5 doesn't work as a relative reference, and that's what I need. Is there a way to separate the Sheets part of the Countif range from the cell reference?
mrempel15 January 2021respondedor
Donald, there are a few changes that need to be made to your formula for this to work. I just had the same problem with another client. Can you send me a copy of your workbook so I can see how the data is structured and create the correct formula? Send firstname.lastname@example.org
JimMarch 12, 2021respondedor
Thanks for posting this. Can I ask a related question? I have a list of names of people who have attended our last 3 annual meetings. I created 3 worksheets, each with one column: the name of the participant. I want to create something that shows how many meetings each person has attended. Can you point me in the right direction?
mrempelMarch 12, 2021respondedor
Jim, I see what you want: You have a workbook with 3 worksheets. Each worksheet is the list of attendees for that specific meeting. In a summary worksheet, you want to list all employees and have a formula next to each name that looks at all 3 meeting worksheets and returns a number of how many times they appeared in all 3 worksheets, right? If that's the case, the process outlined in this tutorial is what you need. Create the named range that lists the names of the sheets ("Sheets" in the tutorial), make a unique list for each contributor, so the formula looks like this
Where "A1:A10" would be the largest range in all sheets containing the names, B1 would point to the first name in the list and go to B2, B3, and so on. .
If that doesn't make sense or your situation is different, feel free to send me a copy of your file so I can take a look at it. Send email@example.com
RebecaMarch 31, 2021respondedor
I have a situation where I could use a little help. I have a workbook with 7 sheets. The first sheet is the one that needs a formula. Sheets 2-6 are category sheets with a list of names, each list is different. The last one is just a list of the top names with more information for your reference. I need a formula similar to this but to count the number of times each name appears on the 2-6 sheets. Like a bookmark. I just can't get this formula right. Would you help me out of this?
Rebeca Tamayo, CCA
mrempelMarch 31, 2021respondedor
Rebecca, I'm happy to help. Send me a copy of your workbook with a detailed explanation of what you want the results to look like. Please include a note about which version of Excel I can write this on (Excel 2010, 29013, 2016, Office 365). Send firstname.lastname@example.org
AndréApril 23, 2021respondedor
I automatically create a new sheet every day. The spreadsheet name includes the date and time it was created. On each sheet I have the names of the people arbitrarily divided into different sections. These areas remain the same each day, but the names move randomly between the different groups of areas each day. My goal is to count the number of occurrences of a given name in a given range and to do so over the last 60 sheets (days). The results appear on a separate sheet that appears in front of all other data sheets called the "Heat Map." I have successfully used COUNTIF to detect occurrences of names in ranges for 1 worksheet, but I don't know how to change the formula to count the occurrences of the above 60 worksheets. Can you help?
I look forward to any input!(Video) How To Count Specific Cells Across Multiple Worksheets In Excel
mrempelApril 24, 2021respondedor
We should be able to create a table with the worksheet names, then just add the new worksheet name to the table and it should be included in the totals. If you'd like to send me a copy of your workbook, I can show you what I mean. Send email@example.com
Lucas Belgrove4. October 2021respondedor
Hi, I'm trying to do something similar for people's work clothes, but I have 3 columns A with the quantity B with a shortcode and C with a description.
There are 120 different types of uniforms spread across 26 different arcs.
So I want to be able to count my first sheet which has a list of all the different uniforms.
I hope you can help.
mrempel5. October 2021respondedor
Luke, can you send me a copy of your file and I'll look at it? Send firstname.lastname@example.org
emma22. November 2022respondedor
CAN YOU HELP ME WITH A SPREADSHEET THAT I AM SETTING UP BUT I NEED TO CHANGE IT A LITTLE SO THAT I CAN ADD A NUMBER IN ANOTHER FIELD IF IT MEETS THE CRITERIA
mrempel22. November 2022respondedor
By all means send me a copy of your file and what you need to do. Please also indicate which version of Excel you are using (Microsoft 365 or earlier).
Htet24 January 2023respondedor
Hi Mike, I'd like to ask for your help with my Excel as I'm trying to achieve another goal for my spreadsheet, but I can't. Thanks if you can help me.
mrempel24 January 2023respondedor
I am happy to help. Submit a sample of your file with a detailed description of what you want to email@example.com. Be sure to specify which version of Excel you are using.(Video) Count of a Specific Text / Number / Date in Multiple sheets of an Excel workbook