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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Domenic

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

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows
also if you would provide files ur working with - maybe there is pq solution.
 

YJ69

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

YJ69

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

ADVERTISEMENT

also if you would provide files ur working with - maybe there is pq solution.
How do I upload the file, I can't download the application needed to upload via minisheets, its against company policy.
 

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows
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?
 

YJ69

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

ADVERTISEMENT

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.
 

Domenic

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

YJ69

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

Forum statistics

Threads
1,148,257
Messages
5,745,712
Members
423,969
Latest member
seanguerrero

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