Formula to return count and average for specified column headers

[cjcass]

Hi,
I'm looking for two formulas if possible.
The formulas need to look up column headers and return results for rows that meet the variable criteria specified.
Cell K16 returns the count for rows that meet all the criteria given in J8:K15.
Cell K19 returns the average for the cells in the column detailed in K18 for the rows identified in the the criteria given in J8:K15.
Please note that the data B4:H25 will have gaps (data missing) so these rows need to be ignored if they are not complete and the criteria in J8:K15 will vary in number of entries.
I'm really looking for a formula solution (not vba) in this instance if possible.
I cannot see how to attached the file so I have given the screenshot only.
Any help much appreciated.

 

[Peter_SSs]

I cannot see how to attached the file so I have given the screenshot only.

Not having to manually type out the sample data would be a great help. Have a look at XL2BB as linked in my signature block below.

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[cjcass]

Ok, have updated my account details and installed the XL2BB add-in...
Hopefully this is what is required?

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3		Header1	Header2	Header3	Header4	Header5	Header6	Header7
4		Apples	London	6	1	1	Male	5
5		Apples	Bristol		2	7		4
6		Apples	Bristol	6	5	3	Male	7
7		Pears	Bristol	3	5	3	Male	7
8		Pears	Bristol	4	3	1	Male	5		Header1	Pears
9		Apples	Bristol	2	5	3	Male	6		Header2	Bristol
10		Apples	Bristol	6	4	7	Male	2		Header6	Male
11		Apples	Bristol	2	4	3	Male	3		Header4	>=3
12		Apples	London		2	7	Male	3
13		Pears	London		1	6		2
14		Pears	London	2	1	6	Female
15		Pears	London	6	7	2	Female	6
16		Pears	London	1	1	2	Female	7		Count:	3	<< formula result
17		Apples	London	6		4	Female	3
18		Apples	Manchester		5	3	Male	2			Header7
19		Apples	Manchester	4	2	5		2		Average:	4.7	<< formula result
20		Pears	Manchester	2	7	1	Male	6
21		Apples	Manchester	1	1	5	Female	6
22		Pears	Bristol	3	7	7	Male	2
23		Apples	London	3	7	1	Male	2
24		Apples	London		7	5	Male	7
25		Apples	London	1	2		Female	3
26
Data

 

[Peter_SSs]

Thanks for the profile update and XL2BB data.

That is quite a task for a formula - I don't see an easy way! However, you could try these monsters.
Note that for my formulas to work (hopefully), you need to include an empty column at the right of the table in your range. You will see that I have used across to column I in my ranges.

cjcass 2020-07-07 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3		Header1	Header2	Header3	Header4	Header5	Header6	Header7
4		Apples	London	6	1	1	Male	5
5		Apples	Bristol		2	7		4
6		Apples	Bristol	6	5	3	Male	7
7		Pears	Bristol	3	5	3	Male	7
8		Pears	Bristol	4	3	1	Male	5		Header1	Pears
9		Apples	Bristol	2	5	3	Male	6		Header2	Bristol
10		Apples	Bristol	6	4	7	Male	2		Header6	Male
11		Apples	Bristol	2	4	3	Male	3		Header4	>=3
12		Apples	London		2	7	Male	3
13		Pears	London		1	6		2
14		Pears	London	2	1	6	Female
15		Pears	London	6	7	2	Female	6
16		Pears	London	1	1	2	Female	7		Count:	3
17		Apples	London	6		4	Female	3
18		Apples	Manchester		5	3	Male	2			Header7
19		Apples	Manchester	4	2	5		2		Average:	4.6666667
20		Pears	Manchester	2	7	1	Male	6
21		Apples	Manchester	1	1	5	Female	6
22		Pears	Bristol	3	7	7	Male	2
23		Apples	London	3	7	1	Male	2
24		Apples	London		7	5	Male	7
25		Apples	London	1	2		Female	3
Sheet1

Cell Formulas
Range	Formula
K16	K16	=COUNTIFS(INDEX(B4:I25,0,IF(J8="",COLUMNS(B3:I3),MATCH(J8,B3:I3,0))),K8&"",INDEX(B4:I25,0,IF(J9="",COLUMNS(B3:I3),MATCH(J9,B3:I3,0))),K9&"",INDEX(B4:I25,0,IF(J10="",COLUMNS(B3:I3),MATCH(J10,B3:I3,0))),K10&"",INDEX(B4:I25,0,IF(J11="",COLUMNS(B3:I3),MATCH(J11,B3:I3,0))),K11&"",INDEX(B4:I25,0,IF(J12="",COLUMNS(B3:I3),MATCH(J12,B3:I3,0))),K12&"",INDEX(B4:I25,0,IF(J13="",COLUMNS(B3:I3),MATCH(J13,B3:I3,0))),K13&"",INDEX(B4:I25,0,IF(J14="",COLUMNS(B3:I3),MATCH(J14,B3:I3,0))),K14&"",INDEX(B4:I25,0,IF(J15="",COLUMNS(B3:I3),MATCH(J15,B3:I3,0))),K15&"")
K19	K19	=AVERAGEIFS(INDEX(B4:I25,0,MATCH(K18,B3:I3,0)),INDEX(B4:I25,0,IF(J8="",COLUMNS(B3:I3),MATCH(J8,B3:I3,0))),K8&"",INDEX(B4:I25,0,IF(J9="",COLUMNS(B3:I3),MATCH(J9,B3:I3,0))),K9&"",INDEX(B4:I25,0,IF(J10="",COLUMNS(B3:I3),MATCH(J10,B3:I3,0))),K10&"",INDEX(B4:I25,0,IF(J11="",COLUMNS(B3:I3),MATCH(J11,B3:I3,0))),K11&"",INDEX(B4:I25,0,IF(J12="",COLUMNS(B3:I3),MATCH(J12,B3:I3,0))),K12&"",INDEX(B4:I25,0,IF(J13="",COLUMNS(B3:I3),MATCH(J13,B3:I3,0))),K13&"",INDEX(B4:I25,0,IF(J14="",COLUMNS(B3:I3),MATCH(J14,B3:I3,0))),K14&"",INDEX(B4:I25,0,IF(J15="",COLUMNS(B3:I3),MATCH(J15,B3:I3,0))),K15&"")

 

[cjcass]

Hi Peter,
Many thanks for that, they are big formulas but I can work with that.
Thanks again for your time and help with this, that's great

 

[Peter_SSs]

You're welcome. Good luck with it!

 

[cjcass]

Just for my understanding - why the extra column in the reference (Col I)..?

 

[Peter_SSs]

Just for my understanding - why the extra column in the reference (Col I)..?

COUNTIFS compares corresponding rows in each column to see if they meet the criteria so in the Header1 column it looks for Pears and then in the Header2 column it looks for Bristol and only counts rows where both criteria are true. This continues for the other criteria.

The issue is that your criteria range J8:K15 has 8 rows so we have to allow for 8 column comparisons. What to do when we come to J12:K12 which is, at the moment, blank? We need to make sure that rows that have been counted considering the first 4 criteria are not now discounted so we nee to manufacture a column & criteria where every row meets the criteria. I have done that by using that extra (empty) column with the criteria that the row in that column is empty.

Hope that makes enough sense.

 

[Peter_SSs]

A couple of other options depending on who is using the sheet and your preferences, noting though that INDIRECT is a volatile function. These shorten the formulas somewhat.

1. Turn the data into a formal table, label the blank column X and fill with zeros. You could then hide this column.

cjcass 2020-07-07 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3		Header1	Header2	Header3	Header4	Header5	Header6	Header7	X
4		Apples	London	6	1	1	Male	5	0
5		Apples	Bristol		2	7		4	0
6		Apples	Bristol	6	5	3	Male	7	0
7		Pears	Bristol	3	5	3	Male	7	0
8		Pears	Bristol	4	3	1	Male	5	0		Header1	Pears
9		Apples	Bristol	2	5	3	Male	6	0		Header2	Bristol
10		Apples	Bristol	6	4	7	Male	2	0		Header6	Male
11		Apples	Bristol	2	4	3	Male	3	0		Header4	>=3
12		Apples	London		2	7	Male	3	0
13		Pears	London		1	6		2	0
14		Pears	London	2	1	6	Female		0
15		Pears	London	6	7	2	Female	6	0
16		Pears	London	1	1	2	Female	7	0		Count:	3
17		Apples	London	6		4	Female	3	0
18		Apples	Manchester		5	3	Male	2	0			Header7
19		Apples	Manchester	4	2	5		2	0		Average:	4.6666667
20		Pears	Manchester	2	7	1	Male	6	0
21		Apples	Manchester	1	1	5	Female	6	0
22		Pears	Bristol	3	7	7	Male	2	0
23		Apples	London	3	7	1	Male	2	0
24		Apples	London		7	5	Male	7	0
25		Apples	London	1	2		Female	3	0
26
Sheet3

Cell Formulas
Range	Formula
L16	L16	=COUNTIFS(INDIRECT("Table1["&K8&"]"),L8,INDIRECT("Table1["&IF(K9="","X",K9)&"]"),L9,INDIRECT("Table1["&IF(K10="","X",K10)&"]"),L10,INDIRECT("Table1["&IF(K11="","X",K11)&"]"),L11,INDIRECT("Table1["&IF(K12="","X",K12)&"]"),L12,INDIRECT("Table1["&IF(K13="","X",K13)&"]"),L13,INDIRECT("Table1["&IF(K14="","X",K14)&"]"),L14,INDIRECT("Table1["&IF(K15="","X",K15)&"]"),L15)
L19	L19	=AVERAGEIFS(INDIRECT("Table1["&L18&"]"),INDIRECT("Table1["&K8&"]"),L8,INDIRECT("Table1["&IF(K9="","X",K9)&"]"),L9,INDIRECT("Table1["&IF(K10="","X",K10)&"]"),L10,INDIRECT("Table1["&IF(K11="","X",K11)&"]"),L11,INDIRECT("Table1["&IF(K12="","X",K12)&"]"),L12,INDIRECT("Table1["&IF(K13="","X",K13)&"]"),L13,INDIRECT("Table1["&IF(K14="","X",K14)&"]"),L14,INDIRECT("Table1["&IF(K15="","X",K15)&"]"),L15)

2. As above but if you are happy to ensure unwanted criteria rows are marked as such then the formulas can be shortened some more. You can mark the unwanted criteria column 1 cells with whatever text you want, so long as the heading of the "spare column" is the same.

cjcass 2020-07-07 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3		Header1	Header2	Header3	Header4	Header5	Header6	Header7	N/A
4		Apples	London	6	1	1	Male	5	0
5		Apples	Bristol		2	7		4	0
6		Apples	Bristol	6	5	3	Male	7	0
7		Pears	Bristol	3	5	3	Male	7	0
8		Pears	Bristol	4	3	1	Male	5	0		Header1	Pears
9		Apples	Bristol	2	5	3	Male	6	0		Header2	Bristol
10		Apples	Bristol	6	4	7	Male	2	0		Header6	Male
11		Apples	Bristol	2	4	3	Male	3	0		Header4	>=3
12		Apples	London		2	7	Male	3	0		N/A
13		Pears	London		1	6		2	0		N/A
14		Pears	London	2	1	6	Female		0		N/A
15		Pears	London	6	7	2	Female	6	0		N/A
16		Pears	London	1	1	2	Female	7	0		Count:	3
17		Apples	London	6		4	Female	3	0
18		Apples	Manchester		5	3	Male	2	0			Header7
19		Apples	Manchester	4	2	5		2	0		Average:	4.6666667
20		Pears	Manchester	2	7	1	Male	6	0
21		Apples	Manchester	1	1	5	Female	6	0
22		Pears	Bristol	3	7	7	Male	2	0
23		Apples	London	3	7	1	Male	2	0
24		Apples	London		7	5	Male	7	0
25		Apples	London	1	2		Female	3	0
26
Sheet4

Cell Formulas
Range	Formula
L16	L16	=COUNTIFS(INDIRECT("Table2["&K8&"]"),L8,INDIRECT("Table2["&K9&"]"),L9,INDIRECT("Table2["&K10&"]"),L10,INDIRECT("Table2["&K11&"]"),L11,INDIRECT("Table2["&K12&"]"),L12,INDIRECT("Table2["&K13&"]"),L13,INDIRECT("Table2["&K14&"]"),L14,INDIRECT("Table2["&K15&"]"),L15)
L19	L19	=AVERAGEIFS(INDIRECT("Table2["&L18&"]"),INDIRECT("Table2["&K8&"]"),L8,INDIRECT("Table2["&K9&"]"),L9,INDIRECT("Table2["&K10&"]"),L10,INDIRECT("Table2["&K11&"]"),L11,INDIRECT("Table2["&K12&"]"),L12,INDIRECT("Table2["&K13&"]"),L13,INDIRECT("Table2["&K14&"]"),L14,INDIRECT("Table2["&K15&"]"),L15)

 

[DanteAmor]

You can play with a Pivot Table, it can give you other advantages. For example, you could filter by several elements: Bristol and London.

In Report Filter you filter by Header1, Header2 and Header6
In Row Label you filter by Header4
In values you count Header1 and Averge of Header7

Check the following image with the filters, in the Total row you have the results.