Index Match Row Heading?
 

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater 0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

Index Match Row Heading?
 

are you looking for a sum 0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)0,"O","")&if sum(P28:P6500)0,"P","") &...
or
=if(sum(O23:O6500)0,O2,"")&if sum(P28:P6500)0,P2,"") &...
or
=if(countif(O23:O6500,"0")0,"O","")&if(countif(P 23:P6500,"0")0,"P") & ...
or
....

depending on what you want, different formulas would be appropriate

"Javier Diaz" wrote:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater 0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

Index Match Row Heading?
 

OK BJ, let me look into these. Thanks I'll get back to you shortly.

"bj" wrote:

are you looking for a sum 0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)0,"O","")&if sum(P28:P6500)0,"P","") &...
or
=if(sum(O23:O6500)0,O2,"")&if sum(P28:P6500)0,P2,"") &...
or
=if(countif(O23:O6500,"0")0,"O","")&if(countif(P 23:P6500,"0")0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

"Javier Diaz" wrote:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater 0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

Index Match Row Heading?
 

These won't do the trick. I did a formula like t his a few years back.
Where it would look at the result and its location and return its heading.
For instance it would give me a result of this formula;

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

Which its result would be found via sumproduct in say Q650, it would then
shoot up to Q2 and get its column heading. Sort of like a Match or Index,
Let me see if I can take a few minutes on the side and brainstorm on this one.

"bj" wrote:

are you looking for a sum 0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)0,"O","")&if sum(P28:P6500)0,"P","") &...
or
=if(sum(O23:O6500)0,O2,"")&if sum(P28:P6500)0,P2,"") &...
or
=if(countif(O23:O6500,"0")0,"O","")&if(countif(P 23:P6500,"0")0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

"Javier Diaz" wrote:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater 0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

Index Match Row Heading?
 

did you also want to look at only those rows which met the criteria in G and J?
in this case try something like
=sumproduct(--(G23:G6500=B20),--(J23:J6500=F20),()23:O65000)*column(O23)+(P23:P65 000)*column(P23)+...)
this will give the column number if only one column is greater than 0 and
only one row meets the g and j requirements.

"Javier Diaz" wrote:

These won't do the trick. I did a formula like t his a few years back.
Where it would look at the result and its location and return its heading.
For instance it would give me a result of this formula;

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

Which its result would be found via sumproduct in say Q650, it would then
shoot up to Q2 and get its column heading. Sort of like a Match or Index,
Let me see if I can take a few minutes on the side and brainstorm on this one.

"bj" wrote:

are you looking for a sum 0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)0,"O","")&if sum(P28:P6500)0,"P","") &...
or
=if(sum(O23:O6500)0,O2,"")&if sum(P28:P6500)0,P2,"") &...
or
=if(countif(O23:O6500,"0")0,"O","")&if(countif(P 23:P6500,"0")0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

"Javier Diaz" wrote:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater 0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

Index Match Row Heading?
 

This returns the column header if only one column has data 0

=INDEX(A1:D1,SUMPRODUCT(--(A2:E90)*COLUMN(A:E)))

Does this help?

"bj" wrote:

did you also want to look at only those rows which met the criteria in G and J?
in this case try something like
=sumproduct(--(G23:G6500=B20),--(J23:J6500=F20),()23:O65000)*column(O23)+(P23:P65 000)*column(P23)+...)
this will give the column number if only one column is greater than 0 and
only one row meets the g and j requirements.

"Javier Diaz" wrote:

These won't do the trick. I did a formula like t his a few years back.
Where it would look at the result and its location and return its heading.
For instance it would give me a result of this formula;

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

Which its result would be found via sumproduct in say Q650, it would then
shoot up to Q2 and get its column heading. Sort of like a Match or Index,
Let me see if I can take a few minutes on the side and brainstorm on this one.

"bj" wrote:

are you looking for a sum 0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)0,"O","")&if sum(P28:P6500)0,"P","") &...
or
=if(sum(O23:O6500)0,O2,"")&if sum(P28:P6500)0,P2,"") &...
or
=if(countif(O23:O6500,"0")0,"O","")&if(countif(P 23:P6500,"0")0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

"Javier Diaz" wrote:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater 0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

Index Match Row Heading?
 

.... dooh ... it doesn't for multiple vlaues... back to the drawing board.

"Toppers" wrote:

This returns the column header if only one column has data 0

=INDEX(A1:D1,SUMPRODUCT(--(A2:E90)*COLUMN(A:E)))

Does this help?

"bj" wrote:

did you also want to look at only those rows which met the criteria in G and J?
in this case try something like
=sumproduct(--(G23:G6500=B20),--(J23:J6500=F20),()23:O65000)*column(O23)+(P23:P65 000)*column(P23)+...)
this will give the column number if only one column is greater than 0 and
only one row meets the g and j requirements.

"Javier Diaz" wrote:

These won't do the trick. I did a formula like t his a few years back.
Where it would look at the result and its location and return its heading.
For instance it would give me a result of this formula;

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

Which its result would be found via sumproduct in say Q650, it would then
shoot up to Q2 and get its column heading. Sort of like a Match or Index,
Let me see if I can take a few minutes on the side and brainstorm on this one.

"bj" wrote:

are you looking for a sum 0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)0,"O","")&if sum(P28:P6500)0,"P","") &...
or
=if(sum(O23:O6500)0,O2,"")&if sum(P28:P6500)0,P2,"") &...
or
=if(countif(O23:O6500,"0")0,"O","")&if(countif(P 23:P6500,"0")0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

"Javier Diaz" wrote:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater 0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

Index Match Row Heading?
 

=INDEX(A1:D1,SUMPRODUCT(--(A2:E90)*COLUMN(A:E))/SUMPRODUCT(--ISNUMBER((A2:E9))))

"Toppers" wrote:

This returns the column header if only one column has data 0

=INDEX(A1:D1,SUMPRODUCT(--(A2:E90)*COLUMN(A:E)))

Does this help?

"bj" wrote:

did you also want to look at only those rows which met the criteria in G and J?
in this case try something like
=sumproduct(--(G23:G6500=B20),--(J23:J6500=F20),()23:O65000)*column(O23)+(P23:P65 000)*column(P23)+...)
this will give the column number if only one column is greater than 0 and
only one row meets the g and j requirements.

"Javier Diaz" wrote:

These won't do the trick. I did a formula like t his a few years back.
Where it would look at the result and its location and return its heading.
For instance it would give me a result of this formula;

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

Which its result would be found via sumproduct in say Q650, it would then
shoot up to Q2 and get its column heading. Sort of like a Match or Index,
Let me see if I can take a few minutes on the side and brainstorm on this one.

"bj" wrote:

are you looking for a sum 0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)0,"O","")&if sum(P28:P6500)0,"P","") &...
or
=if(sum(O23:O6500)0,O2,"")&if sum(P28:P6500)0,P2,"") &...
or
=if(countif(O23:O6500,"0")0,"O","")&if(countif(P 23:P6500,"0")0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

"Javier Diaz" wrote:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater 0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

Index Match Row Heading?
 

=INDEX(A1:D1,SUMPRODUCT(--(A2:E90)*COLUMN(A:E))/COUNTIF(A2:E9,"0"))

"Toppers" wrote:

This returns the column header if only one column has data 0

=INDEX(A1:D1,SUMPRODUCT(--(A2:E90)*COLUMN(A:E)))

Does this help?

"bj" wrote:

did you also want to look at only those rows which met the criteria in G and J?
in this case try something like
=sumproduct(--(G23:G6500=B20),--(J23:J6500=F20),()23:O65000)*column(O23)+(P23:P65 000)*column(P23)+...)
this will give the column number if only one column is greater than 0 and
only one row meets the g and j requirements.

"Javier Diaz" wrote:

These won't do the trick. I did a formula like t his a few years back.
Where it would look at the result and its location and return its heading.
For instance it would give me a result of this formula;

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

Which its result would be found via sumproduct in say Q650, it would then
shoot up to Q2 and get its column heading. Sort of like a Match or Index,
Let me see if I can take a few minutes on the side and brainstorm on this one.

"bj" wrote:

are you looking for a sum 0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)0,"O","")&if sum(P28:P6500)0,"P","") &...
or
=if(sum(O23:O6500)0,O2,"")&if sum(P28:P6500)0,P2,"") &...
or
=if(countif(O23:O6500,"0")0,"O","")&if(countif(P 23:P6500,"0")0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

"Javier Diaz" wrote:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater 0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

Index Match Row Heading?
 

"Toppers" wrote...
=INDEX(A1:D1,SUMPRODUCT(--(A2:E90)*COLUMN(A:E))/COUNTIF(A2:E9,"0"))
....

A lot of effort to avoid the array formula

=INDEX(A1:E1,MIN(IF(A2:E90,COLUMN(A1:E1))))

Index Match Row Heading?
 

Ok, let me see how this one works and I'll get back you Toppers. Thanks.
Sorry I didnt get back sooner, but I didnt get alerted via email that I got a
response. Sometimes that works and sometimes it doesnt.

"Toppers" wrote:

=INDEX(A1:D1,SUMPRODUCT(--(A2:E90)*COLUMN(A:E))/COUNTIF(A2:E9,"0"))

"Toppers" wrote:

This returns the column header if only one column has data 0

=INDEX(A1:D1,SUMPRODUCT(--(A2:E90)*COLUMN(A:E)))

Does this help?

"bj" wrote:

did you also want to look at only those rows which met the criteria in G and J?
in this case try something like
=sumproduct(--(G23:G6500=B20),--(J23:J6500=F20),()23:O65000)*column(O23)+(P23:P65 000)*column(P23)+...)
this will give the column number if only one column is greater than 0 and
only one row meets the g and j requirements.

"Javier Diaz" wrote:

These won't do the trick. I did a formula like t his a few years back.
Where it would look at the result and its location and return its heading.
For instance it would give me a result of this formula;

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

Which its result would be found via sumproduct in say Q650, it would then
shoot up to Q2 and get its column heading. Sort of like a Match or Index,
Let me see if I can take a few minutes on the side and brainstorm on this one.

"bj" wrote:

are you looking for a sum 0 or any value greater than 0?
when you say column heading do you mean the O:U designator or a cell in the
column?
add the book and sheet info to ,
=if(sum(O23:O6500)0,"O","")&if sum(P28:P6500)0,"P","") &...
or
=if(sum(O23:O6500)0,O2,"")&if sum(P28:P6500)0,P2,"") &...
or
=if(countif(O23:O6500,"0")0,"O","")&if(countif(P 23:P6500,"0")0,"P") & ...
or
...

depending on what you want, different formulas would be appropriate

"Javier Diaz" wrote:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater 0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

Index Match Row Heading?
 

Won't a pivot table do it for you?



"Javier Diaz" wrote:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater 0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?

Index Match Row Heading?
 

It probably would, but I think with a simple Index Match formula I could get
the result I'm looking for. The spreadsheet is already a monster and it has
pivots and validations list that I'm working to get rid off via automated
data population with these formulas.

"ryguy7272" wrote:

Won't a pivot table do it for you?



"Javier Diaz" wrote:

Ok guys, here's one to work those muscles. So far I have the following
formula:

SUMPRODUCT(--(Test.xls!$G$23:$G$6500=B20),--('[Test.xls]Test'!$J$23:$J$6500=F20),--('[Test.xls]Test'!$O$23:$O$6500)+--('[Test.xls]Test'!$P$23:$P$6500)+--('[Test.xls]Test'!$Q$23:$Q$6500)+--('[Test.xls]Test'!$R$23:$R$6500)+--('[Test.xls]Test'!$S$23:$S$6500)+--('[Test.xls]Test'!$T$23:$T$6500)+--('[Test.xls]Test'!$U$23:$U$6500))

This is just looking for a few criterias and return the sum of a few
columns. Now, I just want it to look if any of those columns have data
greater 0 and return the column heading for that column, it'll only be one
column out of all of those columns that will have data, now, what do i do?