How to count items on multiple sheets in Excel (2023)

You may also like

This post has 22 comments.

1. 

   sessionApril 21, 2020respondedor

   Hi Mike

   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.

   For example.
   Name/User ID/Location/Mobile Device/Laptop

   Total by division
   Total by location

   1. 

      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 tomrempel@excel-bytes.comwith 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/

2. 

   zoe sanchez3. November 2020respondedor

   Hello Michael,

   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?

   Gracias.

   1. 

      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 tomrempel@excel-bytes.com.

3. 

   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
   1. 

      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 atmrempel@excel-bytes.comto discuss further.

4. 

   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

   coluna b
   Line 1 "Lost"
   line 2 5
   Line 3 12
   Line 4 11

   coluna c
   Line 1 "Caught"
   line 2 7
   Line 3 11
   Line 4 13

   Column D
   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.

   1. 

      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 tomrempel@excel-bytes.com

5. 

   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?

   Donald

   1. 

      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 tomrempel@excel-bytes.com

6. 

   JimMarch 12, 2021respondedor

   ¡Hola!
   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?

   ¡Gracias!

   1. 

      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
      =SUMPRODUCT(CONT.IF(INDIRECT("'"&Sheets&"'!"&"A1:A10"),B1))
      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 tomrempel@excel-bytes.com

7. 

   RebecaMarch 31, 2021respondedor

   Hola,
   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?

   Gracias,
   Rebeca Tamayo, CCA

   1. 

      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 tomrempel@excel-bytes.com

8. 

   AndréApril 23, 2021respondedor

   Hi Mike,

   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
   1. 

      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 tomrempel@excel-bytes.com

9. 

   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.
   Gracias,

   1. 

      mrempel5. October 2021respondedor

      Luke, can you send me a copy of your file and I'll look at it? Send tomrempel@excel-bytes.com

10. 

    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

    1. 

       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).

11. 

    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.

    1. 

       mrempel24 January 2023respondedor

       I am happy to help. Submit a sample of your file with a detailed description of what you want to achievemrempel@excel-bytes.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