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!

 

[excelos]

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.

I think I did explain what I want to do in my previous post.

The data is not relevant really, it's just a table with the first row being the titles of the columns and the rest rows being the actual data. The problem is that the titles/columns change in order and number.

E.g. one time you may have:
Product;Volume;Location

And the other time you may have:
Location;ID;Product;Volume

I want formulas to COUNTIFS some data.
To do that I want to identify the criteria column by its title, instead its reference, as its reference may change (see example for Product, one time it's column A and the second is column C).

Also, since the number of rows may vary, I want to specify whole columns in the COUNTIFS and not a specific range in the column (e.g. A:A and not A2:A1000) because some times the rows maybe 100 and sometimes they may be 2000.

Any idea?

Thanks

PS:
I know how to use index/match to find the cell of the title I want to apply the COUNTIFS criteria on. But I don't know how to convert that information into its whole column. E.g. the index/match may return C1 for Product, but how do I parse that as $C:$C for the COUNTIFS criterion?

 

[AlanY]

something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Title1	Title2	Title3	Title4	Title5	Title6	Title7	Title8			No
2	10	20	30	40	50	60	70	80		Title4	10
3	10	20	30	40	50	60	70	80
4	10	20	30	40	50	60	70	80
5	10	20	30	40	50	60	70	80
6	10	20	30	40	50	60	70	80
7	10	20	30	40	50	60	70	80
8	10	20	30	40	50	60	70	80
9	10	20	30	40	50	60	70	80
10	10	20	30	40	50	60	70	80
11	10	20	30	40	50	60	70	80
Sheet1

Cell Formulas
Range	Formula
K2	=COUNTIFS(INDEX(A:H,,MATCH(J2,$A$1:$H$1,0)),">0")

 

[excelos]

something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Title1	Title2	Title3	Title4	Title5	Title6	Title7	Title8			No
2	10	20	30	40	50	60	70	80		Title4	10
3	10	20	30	40	50	60	70	80
4	10	20	30	40	50	60	70	80
5	10	20	30	40	50	60	70	80
6	10	20	30	40	50	60	70	80
7	10	20	30	40	50	60	70	80
8	10	20	30	40	50	60	70	80
9	10	20	30	40	50	60	70	80
10	10	20	30	40	50	60	70	80
11	10	20	30	40	50	60	70	80
Sheet1

Cell Formulas
Range	Formula
K2	=COUNTIFS(INDEX(A:H,,MATCH(J2,$A$1:$H$1,0)),">0")

That's interesting, I didn't know you could specify A:H to include the whole columns.

But what if you do not know that your table ends at H?

That's why I was looking to find the end of the table with an INDEX/MATCH on the whole 1st row and then use that reference to do the COUNTIFS

 

[RoryA]

Why can't you use an actual Table?

 

[AlanY]

That's interesting, I didn't know you could specify A:H to include the whole columns.

But what if you do not know that your table ends at H?

That's why I was looking to find the end of the table with an INDEX/MATCH on the whole 1st row and then use that reference to do the COUNTIFS

you can just extend the index range and match to incl all the data