Sum unordered data across multiple sheets

0
votes

I think sumproduct sumifs indirect is what I need but I fail to see how to construct it;( I have a workbook for logging volunteer hours.

I'm summing up the monthly hours (12 sheets/tables) to FY Totals sheet/table with hours by volunteer. So the sheet/table that I want to use the formula in is FY TOTALS

The workbook consists of 14 sheets: sheet(VOLUNTEERS) has a table(tbl_volunteers) It contains data about the volunteer and the 1st 3 columns are duplicated on all 13 sheets (12 monthly and 1 FY totals) A5[Status] B5[LastName] C5[Firstname]

The Month sheets have the above fields followed by 5 categories with hours per category for each volunteer

The sheet/table FY Totals is identical to Monthly, but I want the categories to sum all 12 months for each volunteer.

So I need to match criteria of [LastName][FirstName] and sum values in [category]D:I

I can send a copy of the file, but not load images here;(

1
vbaexcel-formula
Can I clarify your question? Do you have 12 sheets which have identical setup and you are just trying to sum up values across all 12 sheets? – CallumDA
yes, I tried to just SUM across sheets but if the sheets are sorted differently, (the same name is not in the same row) I get the wrong volunteer data. SO I need to MATCH the FIRST & LAST NAME (col B/C) in each sheet and add values from col D – Cathy Harrington
I think this can be done well without needing complicated formulas. I've posted as an answer -- do let me know if it works for you? – CallumDA
I would love to use a pivot, but...I also have a value called LIFETIME hours I need to add to the mix. I'm not sure how to include this value in a pivot... – Cathy Harrington

1 Answers

0
votes

You could use multiple consolidated ranges.

enter image description here

Notice that I have four different ranges. These could have been in different worksheets and the names aren't necessarily in the same order. A pivot table has been created that sums the hours worked for each person as requested. In this example, they just happen to be the same.

How?

  1. Press Alt+D+P (windows) or cmd+alt+P (mac)
  2. Select multiple consolidated ranges, then Next
  3. Select Create a single page field for me, then Next
  4. Add each of your ranges one by one, then Finish

Update

There's so much more you can do with PivotTables, as per your comments -- you can separate out data by creating your own page fields. Do this instead of step 3 above

  1. Select I will create the page fields, then Next
  2. For each range you select and add, click '1' page field and type the description of that data, for example 2016 Data, or 2017 Data
  3. (Optional) Your fields will automatically be put into the report filter field. You might want to drag it into Row Labels field (below Row) to get the view below

You can then see the split of the different fields you used in your PivotTable - in yellow I have my 2016 data and you can see it's been split out in the PivotTable.

enter image description here