AutoFill formula in a column based on data in a row

[armsafna]

Say I have an attendance list for a class:

Student	Class 1	Class 2	Class 3	Total
Alpha	X	X	X	COUNTIF(B2:C2,"X") = 3
Bravo	X			1
Charlie	X	X	X	3
Delta	X		X	2
Echo	X	X	X	3
Foxtrot	X	X	X	3
Golf	X	X	X	3

The Class1:Class3 fields are data-validated to have Xs or Blanks depending on whether a student is present or not.
The Total column counts Xs in the corresponding row to get an attendance total for the student.

I have another table tracking Class data, as opposed to student date:

Class	Attendance total
1	=COUNTIF(Table1[B2:B8],"X") = 8
2
3

I use a COUNTIF function in the 'Attendance Total' column to add Xs in the corresponding class column in the other table. But... the formula doesn't AutoFill properly!
Can I not AutoFill a formula in a column based on a pattern that varies by row?
Any workarounds?

Trying to complete report card season in 5 minutes.

 

[armsafna]

oops the COUTNIF in 'Attendance Total' should equal 7 guess I can't math without excel L.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Student	Class 1	Class 2	Class 3	Total		Class	Attendance total
2	Alpha	X	X	X	3		1	7
3	Bravo	X			1		2	5
4	Charlie	X	X	X	3		3	6
5	Delta	X		X	2
6	Echo	X	X	X	3
7	Foxtrot	X	X	X	3
8	Golf	X	X	X	3
9
Data

Cell Formulas
Range	Formula
H2:H4	H2	=COUNTIF(INDEX($B$2:$D$8,,MATCH($G$1&" "&G2,$B$1:$D$1,0)),"X")
E2:E8	E2	=COUNTIF(B2:D2,"X")

 

[armsafna]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Student	Class 1	Class 2	Class 3	Total		Class	Attendance total
2	Alpha	X	X	X	3		1	7
3	Bravo	X			1		2	5
4	Charlie	X	X	X	3		3	6
5	Delta	X		X	2
6	Echo	X	X	X	3
7	Foxtrot	X	X	X	3
8	Golf	X	X	X	3
9
Data

Cell Formulas
Range	Formula
H2:H4	H2	=COUNTIF(INDEX($B$2:$D$8,,MATCH($G$1&" "&G2,$B$1:$D$1,0)),"X")
E2:E8	E2	=COUNTIF(B2:D2,"X")

Oh that works perfectly. So it basically says to count the Xs in the attendance table starting with the first row (because row # isn't specified) and in the column that matches a specific expressed during lookup as a concatenation of the word "Class" and the class number - does that sound right?

Can INDEX and MATCH be nested as such to avoid the column to row formula fill dilemma in general?

 

[Fluff]

does that sound right?

Yup.

Can INDEX and MATCH be nested as such to avoid the column to row formula fill dilemma in general?

Not quite sure what you mean by this.

 

[armsafna]

Not quite sure what you mean by this.

If I have a series of columns in Table1 with Xs in them which I want to count and output in a single column in Table2, with each cell in that column in Table2 corresponding to all the Xs in a column in Table1. So for example =COUNTIF(Table1[Column1],"X") counts the Xs in Column1 of Table1 and outputs it in Column2 of Table2 (because Column1 in Table2 is just the name of the lesson). If I drag the formula from the first cell in Column1 of Table2 downward through the column to autocomplete, what I want it to do is understand that I want =COUNTIF(Table1[Column2],"X") and then =COUNTIF(Table1[Column3],"X"), but it doesn't recognize the pattern.

What you have done with =COUNTIF(INDEX(Table1[[Column1]:[Column3],,MATCH([@Column1],Table1[#Headers],[Column1]:[Column3],0)),"X") is basically made it so that everything is absolute reference except for the Lookup Value in MATCH (which tracks down Column1) so now if I try and complete the Formula in a column, it is doing what it seems to want to do which is change the formula to move one cell down in Column1 in Table2 per cell I move down in Column2 in Table2.

This might make no sense but overall the INDEX and MATCH combo seems to erase a lot of the ambiguity tysm.

 

[Fluff]

Still not quite sure what you mean. Does the formula you posted do what you want?

 

[armsafna]

Still not quite sure what you mean. Does the formula you posted do what you want?

yes it does! works very well.

 

[Fluff]

So all's good