# Checking multiple worksheets for criteria and summing data when met

#### chrissyrw

##### New Member
Hello Team,

I have a workbook that has multiple worksheets tracking individuals' monthly usage of an online learning system (number of chapters passed, number of logins, etc). Each worksheet is a separate month. Also, individuals are associated with a particular group (Group A, Group B, etc.).

A separate worksheet is dedicated to provide a summary based on the input criteria such as the particular group and month, or all groups. The summary worksheet sums the total number of logins, chapters passed, failed, and so on of the selected group and month.

Two drop down menus are shown to select the desired group and month. For Example, Group A and May. I've been successful at displaying information for just one particular group or all groups using the SUMIF formula, but only for one month. I need assistance with Excel checking every worksheet for the specified month (located in Cell B1), so if we want a summary of April's usage data for a particular group, that data only will be shown.

I've been researching different formulas and macros, but haven't found anything that quite answered my concern. What would be the best way to accomplish this?

Let me know if I can clarify further. Thanks for your assistance!

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### trunten

##### Active Member
I'd probably just use an indirect formula. For this I am going to make the following assumptions:
- The values for Group and Month are stored in cells A1 and B1 on the summary sheet respectively.
- The monthly sheets are named after the corresponding month ('January', 'February', etc)
- The columns containing the group name will be column B of the monthly sheets
- The columns you wish to sum will be column C of the monthly sheets

With all those in place you could do this:

=SUMIFS(Indirect(B1&"!C:C"), Indirect(A1&"!B:B"), A1)

hth.

P.s. If you're wondering why SUMIFS instead of SUMIF that's just a habit of mine. I find it easier to amend in the future if I decide I need another condition.

#### chrissyrw

##### New Member
Hi Trunten,

Thank you for your reply. My apologies for being unclear about the data. The 'Group' is a column within the data. The data is exported from the online learning platform into an Excel file. The month is located in cell B1, but the remaining information including the Group in is the table itself. I've included a sample screenshot below (I've changed the users' names and removed identifying information for security purposes). In reality, we have 2500+ users and 20+ groups all consolidated onto one spreadsheet. The challenge is two-fold now that I have given it more thought. First, we need Excel to locate the right worksheet based on the month chosen in the drop-down menu. Second, we need it to only sum the data (for example: total sign-ins for all users ) if the criterion matches that of the 'Group Name' column.

Sample of May Worksheet:

Sample Summary Worksheet, Cells B3 & B5 are drop-down menus (function first then aesthetics ):

Thanks again for the help!

#### chrissyrw

##### New Member
Bumping for the late morning/early afternoon crew!

Replies
0
Views
1K
Replies
0
Views
585
Replies
0
Views
493
Replies
1
Views
567
Replies
0
Views
1K

1,195,631
Messages
6,010,783
Members
441,569
Latest member
PeggyLee

### 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.

### Which adblocker are you using?

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

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