Forum Discussion

craigs1970's avatar
craigs1970
Copper Contributor
Jan 07, 2024

help on an excel project

Looking for help from people with better Excel knowledge than me. I have a work book with various tabs at the bottom relating to specific items of equipment (eg 6 tabs). Each of these tabs has multiple columns (8), the first column is name- This relates to an individual staff member /person who has used that piece of equipment. the remaining columns all have numbers in them. There are multiple people that use the equipment, some people will use all of the equipment others might only use 2 of the 6. What I am looking for is help creating say a 7th Tab that will list each person who has used the equipment and give a total based on each column. Eg Person "Ed" uses all 6 pieces of equipment, and in column 2 of the first tab he has a value of 2, second tab it is 4 , third tab it is 20, 4th tab it is 14, 5th tab it is 201 and final tab it is 1= total 242. Person "bob" uses only 4 pieces of equipment, in column 2 of the first tab he has a value of 3, second tab it is 4 , third tab his name is not there as not used the equipment , 4th tab it is 14, 5th tab it is 143 and final tab his name is not there as not used the equipment = total 164 . What I need is that 7th tab to have "Ed" in the name column with 242 in the second column, "bob" in the name column and 164 in the second column. I have around 250 different staff names. Any ideas or direct me to a video that shows me!!

BI & Data Analysis
excel
Formulas and Functions

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jan 07, 2024

    craigs1970 

    If I understood correctly,you can use a combination of functions like SUMIFS, INDEX, MATCH, and IFERROR to summarize the data from multiple tabs onto a summary tab.

    Assuming the following:

    • Your equipment tabs are named Equipment1, Equipment2, ..., Equipment6.
    • In each equipment tab, the names are in column A and the numbers are in column B (assuming for the explanation that the numbers are in column B).

    Follow these steps to create a summary tab:

    1. Create a list of unique names:

      • In your new summary tab (7th tab), in column A, list all unique names from all equipment tabs. You can do this manually or use the following formula in A2 and drag down:

    =UNIQUE(FILTER(Equipment1!A:A, Equipment1!A:A<>"") & FILTER(Equipment2!A:A, Equipment2!A:A<>"") & ...) 

    2. Calculate totals for each person:

      • In column B of your summary tab, use the following formula in B2 and drag down:

    =SUMIFS(Equipment1!B:B, Equipment1!A:A, $A2) + SUMIFS(Equipment2!B:B, Equipment2!A:A, $A2) + ...

    This formula sums the values in each equipment tab based on the person's name.

    3. Handle the case where a person did not use a specific equipment:

      • In case a person didn't use a specific equipment, the SUMIFS would return an error. You can use IFERROR to handle this. Modify the formula in B2 as follows:

    =IFERROR(SUMIFS(Equipment1!B:B, Equipment1!A:A, $A2), 0) + IFERROR(SUMIFS(Equipment2!B:B, Equipment2!A:A, $A2), 0) + ...

    This ensures that if there's an error (person not found in a specific equipment tab), it's treated as 0.

    Now, you should have a summary tab that lists each person and their total usage based on the values in the equipment tabs.

    Note: The formula examples provided assume the structure I assumed for your sheets. You may need to adjust column references based on the actual structure of your workbook. The text was revised with the AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

Resources