Excel 365 help needed to summarise values from multiple sheets.

YJ69

New Member
Joined
Jun 23, 2021
Messages
13
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: 21

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
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...
 

YJ69

New Member
Joined
Jun 23, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
Try...

D3, copied down:

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

Hope this helps!
 
Solution

YJ69

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

ADVERTISEMENT

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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
That's great, glad I could help, cheers!
 

YJ69

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

ADVERTISEMENT

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?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
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!
 

YJ69

New Member
Joined
Jun 23, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Again Domenic, that works perfectly. I won't claim to understand it but it does just what I need. thankyou!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,826
Office Version
  1. 365
Platform
  1. Windows
That's great, glad I could help, cheers!
 

Forum statistics

Threads
1,148,255
Messages
5,745,691
Members
423,968
Latest member
LTCASA

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
Top