Can you use XLOOKUP and COUNTA together?

[jodilynnem]

I would like column B in the 'Analysis' table below to count how many subjects there are for each candidate in the 'Results' table. However I would like to try and do this using header names as opposed to cell references as this is going to be a very large table which will keep growing. I was thinking there might be a way to combine XLOOKUP with COUNTA but cant work it out. Any ideas? Unfortunately I can't have all of the subject columns next each other, they need to be separated by their scores.

Table Name: Analysis

Table Name: Results

 

[Peter_SSs]

Welcome to the MrExcel board!

I would like to try and do this using header names as opposed to cell references

What exactly do you mean by that?
What relevance does that have to the size of the table?

What are the expected results for that sample data and why? Can you post again with the expected results and using XL2BB so that we can easily copy for testing?

 

[jodilynnem]

Welcome to the MrExcel board!


What exactly do you mean by that?
What relevance does that have to the size of the table?

What are the expected results for that sample data and why? Can you post again with the expected results and using XL2BB so that we can easily copy for testing?

Thank you, although I think I have just worked it out. I am going to use the following formula in column B of the Analysis table:

=COUNTA(Results[@[Sub 1]],Results[@[Sub 2]],Results[@[Sub 3]],Results[@[Sub 4]],Results[@[Sub 5]],Results[@[Sub 6]],Results@[Sub 7]])

Turned out I wasn't putting the extra brackets around the header reference which is why it wasn't working.

 

[awoohaw]

This function may work a little better for you as far as ease in adding new subjects:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ID Number	Nbr of Scores
2	12345678	5
3	12345677	4
4	12345676	7
5	12345675	6
6	12345674	6
7
8
9
10		Sub 1	Score 1	Sub 2	Score 2	Sub 3	Score 3	Sub 4	Score 4	Sub 5	Score 5	Sub 6	Score 6	Sub 7	Score 7
11	12345678	VSE	8	QQU	7	GTU	2	LFU	6	GFV	9
12	12345677	TIX	6	TVA	8	DRG	5	WPX	6
13	12345676	RDK	3	RGM	6	TPK	6	HRC	9	OSK	4	PKW	4	PTJ	4
14	12345675	SVA	6	KWJ	9	XNZ	8	WZG	4	XWZ	2	UPU	8
15	12345674	NLV	7	HYZ	2	JPZ	7	FSF	2	UZX	9	EHE	7
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=SUM((--(ISTEXT($B$11:$O$15)))*(ISNUMBER(FIND("Sub",$B$10:$O$10)))*(A2=$A$11:$A$15))

 

[jodilynnem]

Thank you @awoohaw I will give this a try!

 

[Peter_SSs]

What about this shorter version? Automatically expands/contracts if the results table is increased/decreased.

Results table

jodilynnem.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ID Number	Sub 1	Score 1	Sub 2	Score 2	Sub 3	Score 3	Sub 4	Score 4	Sub 5	Score 5	Sub 6	Score 6	Sub 7	Score 7
2	12345678	Bi	8	Ch	8	En	6	Lit	6	Fr	5
3	12345677	Ar	6	Bi	7	Ch	6	Ph	6
4	12345676	Ar	8	Bi	8	Dr	8	En	8	Lit	8	Fr	7	Gg	8
5	12345675	Ar	9	Ch	7	En	6	Lit	7	Gg	7	Ma	7
6	12345674	Ar	9	Ch	7	Hi	7	Cn	5	En	7	Lit	7
Sheet1

Analysis (my formula in col B, yours in C)

jodilynnem.xlsm
	A	B	C
1	ID Number	Number of Scores	Number of Scores2
2	12345678	5	5
3	12345677	4	4
4	12345676	7	7
5	12345675	6	6
6	12345674	6	6
Sheet2

Cell Formulas
Range	Formula
B2:B6	B2	=COUNT(FILTER(Results,Results[ID Number]=[@[ID Number]]))-1
C2:C6	C2	=COUNTA(Results[@[Sub 1]],Results[@[Sub 2]],Results[@[Sub 3]],Results[@[Sub 4]],Results[@[Sub 5]],Results[@[Sub 6]],Results[@[Sub 7]])

 

[jodilynnem]

Great, thank you @Peter_SSs

 

[Peter_SSs]

Great, thank you @Peter_SSs

You're welcome. Thanks for the follow-up.