Checking multiple worksheets for criteria and summing data when met

chrissyrw

New Member
Joined
Jun 9, 2015
Messages
7
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0
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:


Example2.png


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

Example3.png



Thanks again for the help!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

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