Excel 365 help needed to summarise values from multiple sheets.

YJ69

New Member
Joined
Jun 23, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi

I have a workbook (office 365) of class attendances with a summary sheet for the classes and also for the attendees.

Each of the class sheets contains a table with date and total (how many times they have attended that particular class) columns. Attendees can join multiple sessions and classes. I want to count how many classes (the number of session sheets) that each person features on. I was using this formula which worked =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!"&"A9:A43"),B4)) but for some reason now doesn't.

I also then want to summarise how many sessions (across all classes) individuals have attended. I feel it should be easy but I'm really struggling so any help much appreciated.
 

Attachments

  • Classes.jpg
    Classes.jpg
    155.2 KB · Views: 23

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
I've checked for spaces before and after the names and there aren't any, some do end with a number though, would this cause a problem?
No, that wouldn't be a problem...
 
Upvote 0
I managed to correct the problem with the formula - thank you for your suggestions.

Now I just need to work out how to find a name across all the sheets and sum the corresponding total column figures. Again any suggestions much appreciated.
 
Upvote 0
Try...

D3, copied down:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"A9:A43"),B3,INDIRECT("'"&Sheets&"'!"&"O9:O43")))

Hope this helps!
 
Upvote 0
Solution
Try...

D3, copied down:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!"&"A9:A43"),B3,INDIRECT("'"&Sheets&"'!"&"O9:O43")))

Hope this helps!
Thank you so much, that works perfectly! My workbook it complete.
 
Upvote 0
So this worked perfectly... but, I now have a number of class sheets with the totals all in column Q. Some are fine that way but some need more columns adding in for additional classes ie, the total column will be moved for some sheets. Do I need to change all the sheets so they read from the same column or is it possible to refence column header from all the sheets?
 
Upvote 0
You don't necessarily need to change your class sheets, but the needed formula would be very complex, and even more inefficient that the one that you already have. If you would like to give it a try, here goes.

First make sure that you list your sheet names in a vertical range of cells, not a horizontal one. Then, assuming that for each class sheet, the data starts at Column E and ends at Column Z, and that Row 8 contains the headers, and that one of the cells in Row 8 contains the header/label "Total Participant Attendance", try...

D3, copied down:

VBA Code:
=SUM(SUMIF(INDIRECT("'"&Sheets&"'!A9:A43"),B3,OFFSET(INDIRECT("'"&Sheets&"'!E9:Z43"),,MMULT(IF(LEFT(T(OFFSET(INDIRECT("'"&Sheets&"'!E8:Z8"),,COLUMN(INDIRECT("E8:Z8"))-COLUMN(INDIRECT("E8")),,1)),5)="Total",COLUMN(INDIRECT("E8:Z8"))-COLUMN(INDIRECT("E8")),0),TRANSPOSE(COLUMN(INDIRECT("E8:Z8")))^0),,1)))

. . . and adjust the ranges accordingly. If you need help in adjusting ranges, post back.

Hope this helps!
 
Upvote 0
Again Domenic, that works perfectly. I won't claim to understand it but it does just what I need. thankyou!
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top