Formula or UDF to add figures by code #

M

mikeburg

I have a list of figures in column A & each figure's 3 digit code i
column B.
What would be a cell formula that could be put in columns C & D t
subtotal by code?

Example

_ ___A__ __B__ __C___ _D_
1| 100.00 | 105 | 211.15 | 105
2| 115.06 | 118 | 315.06 | 118
3| 122.15 | 107 | 272.15 | 107
4| 111.15 | 105 |
5| 200.00 | 118 |
6| 150.00 | 107 |

I am trying to do this without VBA, only formulas or possibly a use
defined function. However, if it can't be done with formulas or
function then VBA would help a lot!

Thank you for all the help, mikebur
 
M

mikeburg

Great!

However, need cells in column D to come up with the code automatically
& put it in the next row of column D without skipping any cells.

It extremely important that column C's values continue to depend on the
code in column D.

Thanks a million. mikeburg
 
M

Max

One non-array formulas approach ..

Put in C1: =IF(D1="","",SUMIF(B:B,D1,A:A))
(just a slight add to JE's suggestion)

Put in D1:
=IF(ISERROR(SMALL(E:E,ROWS($A$1:A1))),"",
INDEX(B:B,MATCH(SMALL(E:E,ROWS($A$1:A1)),E:E,0)))

Put in E1:
=IF(B1="","",IF(COUNTIF($B$1:B1,B1)>1,"",ROW()))

Select C1:E1, copy down as far as required, say to E100
(can copy down ahead of expected data in cols A and B)

Cols C and D will return what you want
 
M

mikeburg

Works great! I just wish we did not have to us the additional column E,
but I can live with it if we have to! Thanks a million! mikeburg
 
Top