Summing numbers based on multiple conditions across a range

Hi docstew. Welcome to the forum.

You almost always get faster/more responses and faster solution if you upload a small Excel workbook file (instead of a screenshot). Make sure it is representative of what you are working with. It saves volunteers from regenerating the data and gives context to the questions.

The 'paperclip' icon does not work for this. Use this method instead.

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

I am having difficulty understanding. So I need to take this in steps.

For starters I would suggest some helper rows and a helper column in 'IOC Rollup'.

In order to make the column headers consistent with headers in 'TIA Matrix' insert two rows above the current column headers. Then parse those with these formulas. In C2:K2

Formula:

Please Login or Register  to view this content.

and C3:K3

Formula:

Please Login or Register  to view this content.

The offset section headers in column A 'IOC Rollup' cause havoc for formulas similar to merged cells. The helper in column O is a remedy for this.

Formula:

Please Login or Register  to view this content.

I need to examine the details in both sheets to determine what my other questions are. At the moment I have difficulty relating some of the items across both sheets.

Try this much and see if it helps so far.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2				LPC	LPC	FC1	FC1	FC2	FC2	FC3	FC3
3				Questions	High Missed	Questions	High Missed	Questions	High Missed	Questions	High Missed
4			Lectures	LPC Questions	LPC High Missed	FC1 Questions	FC1 High Missed	FC2 Questions	FC2 High Missed	FC3 Questions	FC3 High Missed	Total Questions	Total High missed	Percentage	Helper	Test Version taken
5	Team 1	SFC S	8	16	1	17		15		15		63	1	2%	Team 1	LPC	B
6		SSG D	3	9	0	3		0		0		12	0	0%	Team 1	FC1	B
7		SSG F	7	10	0	10		17		5		42	0	0%	Team 1	FC2	A
8		SSG J	7	8	0	5		5		21		39	0	0%	Team 1	FC3	C
9		SSG R	7	7	0	15		13		9		44	0	0%	Team 1
10	Team 2	SFC R	0	0	0	0		0		0		0	0		Team 2
11		SSG Ch	0	0		0		0		0		0	0		Team 2
12		SSG Cu	0	0		0		0		0		0	0		Team 2
13		SSG G	0	0		0		0		0		0	0		Team 2
14		SSG N	0	0		0		0		0		0	0		Team 2
15	Team 3	SSG W	0	0		0		0		0		0	0		Team 3
16		SSG So	0	0		0		0		0		0	0		Team 3
17		SSG Su	0	0		0		0		0		0	0		Team 3
18		SSG T	0	0		0		0		0		0	0		Team 3
19		SSG V	0	0		0		0		0		0	0		Team 3

There are inconsistencies in the two sheets. 'TIA Matrix' makes a distinction between LPC-A, LPC-B and LPC-C. 'IOC Rollup' does not. It appears you wish to sum only LPC-A. What is the roll of the other LPCs?

Edit Also it appears the formula in column E 'IOC Rollup' "LPC High Missed" references the column "High Missed" for Team 1 in 'TIA Matrix'.

The assignment is becoming less clear the more I look at this.
I am most confused.

Can you help us out?

If I understand correctly then the following may work in summing the number of questions per version of each test.
Helper columns are added to the Version Breakdown sheet.
Column D is populated using: =IF(ISTEXT(A2),LEFT(A2,3),D1)
Columns E:G are populated using: =IF(ISNUMBER(A3),A3,"")
On the IOC Rollup sheet, R3:R6 are populated using*:

Formula:

Please Login or Register  to view this content.

*Denotes and array entered formula which is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula.
As to the "...count for each instructor's lectures and how many questions they have on the test if they move between classrooms..." we may be better able to help if you would give an example or two of which numbers on the IOC Rollup sheet are "weird" and what the expected value should be (and perhaps why the value is expected)
Let us know if you have any questions.