PDA

View Full Version : Sum same elements



aquayle
11-07-2006, 09:44 AM
Difficult to explain, a picture of the workflow might do it!


Sheet1 contains unsorted info. sheet 2 contains look-up text relating to the numbers.
Sheet 3 should sum up the amount of money in all sector 1 (Companies from sheet2) and add the name as a string.... hmm.
Got it working so far, sorts the data on sheet 1, reads the text from sheet 2.

Now have:

Name ? Sector
abc 123 1
plc 111 1
ghy 333 2
xyz 199 2

how do I add all the ? (2nd column) where column 3 = 1 and then where column 3 =2 (and so on ...might go to 99999)

Ta - Andy
there should be a picture attached...

Bob Phillips
11-07-2006, 12:10 PM
On Sheet3

Cell B2, add

=SUMIF(Sheet1!C:C,INDEX(Sheet2!A:A,MATCH(A2,Sheet2!B:B,0)),Sheet1!B:B)

Select C2, and as many cells across as you will ever need, and in the formula bar enter

=IF(ISERROR(TRANSPOSE(SMALL(IF(Sheet1!$C$1:$C$20=INDEX(Sheet2!$A1:$A20,MATC H($A2,Sheet2!$B1:$B20,0)),ROW($A$1:$A$20),""),ROW($A$1:$A$20)))),"",
INDEX(Sheet1!$A$1:$A$20,TRANSPOSE(SMALL(IF(Sheet1!$C$1:$C$20=INDEX(Sheet2!$ A1:$A20,MATCH($A2,Sheet2!$B1:$B20,0)),ROW($A$1:$A$20),""),ROW($A$1:$A$20)))))

The second formula is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

Then copy down the rows.