| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)))) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| Index heading when cell is populated | Excel Worksheet Functions | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
| index,match,match on un-sorted data | Excel Worksheet Functions | |||
![Javier Diaz[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
