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 multip...
BI & Data Analysis
excel
Formulas and Functions
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