How to use names of columns instead of references?

[excelos]

Hello

I have a sheet with the first row being the titles of the columns.

I want to use COUNTIFS but I don't want to use the references of the columns as these may change.

I want to use instead the names of the columns.

I use INDEX/MATCH to locate the name of the column I need.

How can I use that information in COUNTIFS so that I specify the column $A:$A to contain the criteria I need?

Thanks!

 

[Fluff]

Something along the lines of

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Longitude	Easting	Northing	GridRef	County	District		County
2	-2.655301	354569	173596	ST545735	Somerset	North Somerset		Norfolk	3
3	-1.344331	443249	437642	SE432376	West Yorkshire	Leeds
4	-1.275226	450212	197760	SU502977	Oxfordshire	Vale of White Horse
5	-1.289136	449258	196951	SU492969	Oxfordshire	Vale of White Horse
6	-3.190212	323218	524128	NY232241	Cumbria	Allerdale
7	1.54832	640180	310380	TG401103	Norfolk	Broadland
8	-2.113507	392835	566041	NY928660	Northumberland	Northumberland
9	-1.290897	448764	235209	SP487352	Oxfordshire	Cherwell
10	-1.871541	408530	449548	SE085495	West Yorkshire	Bradford
11	-2.606423	359983	413693	SD599136	Lancashire	Chorley
12	-2.605033	360065	412521	SD600125	Lancashire	Chorley
13	-2.135258	391089	381329	SJ910813	Cheshire	Cheshire East
14	-1.610059	425448	494294	SE254942	North Yorkshire	Hambleton
15	-0.900953	472628	425079	SE726250	East Riding of Yorkshire	East Riding of Yorkshire
16	1.135007	614834	248873	TM148488	Suffolk	Mid Suffolk
17	-2.277924	381289	304406	SJ812044	Shropshire	Shropshire
18	1.313923	625314	288111	TM253881	Norfolk	South Norfolk
19	0.094746	544198	225189	TL441251	Hertfordshire	East Hertfordshire
20	1.265345	619946	333551	TG199335	Norfolk	North Norfolk
Sheet1

Cell Formulas
Range	Formula
I2	=COUNTIF(INDEX(A2:F20,0,MATCH(H1,A1:F1,0)),H2)

 

[AlanY]

select all columns with data, from menu > Formulas > Create from selection > pick top row

 

[excelos]

What does the INDEX(A2:F20,0,MATCH(H1,A1:F1,0)) return?

It searches for the whole table A2:F20 in row zero. I don't get that bit. Is it the row of the A2?
Then it finds the County in the row with the titles which is 5.

So which the return of the INDEX is the cell column 0 and row 5.

How does that specify the column E:E?

 

[Fluff]

It returns the entire column (row 2 to row 20) where County is found in row 1

 

[excelos]

Also, the problem is that I need to know the bottom right cell of the table.
I do not always know that.
Is there a work around?

 

[excelos]

To phrase it in other words:

I want to countif the cells, that a different column has a specific value.

I need to specify this column by the name of its title in the first row.

I want this column to be unlimited length, i.e. $A:$A (with A to be the column with the specific column title mentioned in the first row)

If it cannot be unlimited length, I want the length to extend to the either the lowest cell with data in the whole sheet or to the lowest cell with data in a specific column.

Any ideas?

 

[Fluff]

To get the entire column just use
=COUNTIF(INDEX(A:F,0,MATCH(H1,A1:F1,0)),H2)

 

[excelos]

To get the entire column just use
=COUNTIF(INDEX(A:F,0,MATCH(H1,A1:F1,0)),H2)

Thanks but I do not know the F, I do not know if I will cover the whole table with A:F or I will need A:G, or A:H etc.

I will know the F,G,H if I do an index/match for the title of the column I want, but how do I do feed that info into the A:F bit?

 

[Fluff]

It would help if you gave some exact information, so far you have given us nothing to work with.
I am not willing to keep guessing at what you are trying to do.
Please explain what you are trying to do, where your data is, where your criteria are etc, etc.