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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
For that formula to work, you need to enter the relevant sheet names in a range of cells, and name that range "Sheets". Have you done that?
 
Upvote 0
For that formula to work, you need to enter the relevant sheet names in a range of cells, and name that range "Sheets". Have you done that?
Hi, yes I've names the range and checked that all sheets I need referenced are included in it.
 
Upvote 0
minisheet is for excel whch i dont need.
i need the txt file ur working with.

can you prepare as big file but randomize and use lets say onedrive share option?
 
Upvote 0
minisheet is for excel whch i dont need.
i need the txt file ur working with.

can you prepare as big file but randomize and use lets say onedrive share option?
Sorry, I don't know what you mean, this is an excel file.
 
Upvote 0
Maybe one or more sheets listed in your named range Sheets do not exist?

Or maybe one or more sheet names listed in Sheets contain spelling errors, or leading spaces, or trailing spaces?

Or maybe Sheets contains one or more blank/empty cells?
 
Upvote 0
Maybe one or more sheets listed in your named range Sheets do not exist?

Or maybe one or more sheet names listed in Sheets contain spelling errors, or leading spaces, or trailing spaces?

Or maybe Sheets contains one or more blank/empty cells?
The list is created directly from the title cell (within the class sheets) which in turn reads directly from the tab name. 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?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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