Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Summarising table entries
Thanks Biff
My actual sheets have a lot more columns (and may be added to or reduced later) and ideally I want to run the summary for each of the values in the cells (i.e. 1, 2, 3) in a separate end column. I think having to create a new column for each of the headed columns, and do it 3 times, will mean I will run past the IV column limit. Do you know what that add-in function is called? I've had a look at 'Conditional sum' and 'Lookup' but can't see what I'm after. Thanks for your help. Guy "Biff" wrote: Hi! Not EXACTLY what you want, but close. In F2 enter this formula and copy across to I2: Entered as an array with the key combo of CTRL,SHIFT,ENTER. =IF(ISERROR(INDEX($B$1:$E$1,SMALL(IF($B2:$E2=1,COL UMN ($A:$D)),COLUMN(A:A)))),"",INDEX($B$1:$E$1,SMALL(I F ($B2:$E2=1,COLUMN($A:$D)),COLUMN(A:A)))) This will extract the headings to separate cells. There is an add-in with a specialized function that does exactly what you want but I'm not familiar with it. Another way to get close to what you want is to enter the formula in say G2 and copy across to J2. Then, in F2 enter this formula: =TRIM(G2&" "&H2&" "&I2&" "&J2) This will extract the headings to separate cells G2:J2 then the formula in F2 will concatenate those cells into a single cell less the comma. Biff -----Original Message----- Hi, I wonder if anyone can help. I have a spreadsheet that looks like this: A B C D E F 1 One Two Three Four Intended result 2 Fred 1 2 3 1 One, Four 3 Ben 2 1 1 1 Two, Three, Four I am trying to work out a way to summarise the above table in the the form of column 'F' (intended result). I.e. the resulting cell will gather and join the headings of the columns where a '1' appears below it. I've toyed with sumproduct but it seems to only work with numerical values in the result. Is there a way to do this in Excel 97? Thanks. Guy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
table | Excel Discussion (Misc queries) | |||
table | Excel Worksheet Functions | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) | |||
Data Table - does it work with DDE links and Stock Tickers? | Excel Worksheet Functions | |||
How I can add categories in a pivot table? | Excel Worksheet Functions |