Formula or UDF to add figures by code #
 

I have a list of figures in column A & each figure's 3 digit code in
column B.
What would be a cell formula that could be put in columns C & D to
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 user
defined function. However, if it can't be done with formulas or a
function then VBA would help a lot!

Thank you for all the help, mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=467735

One way:

C1: =SUMIF(B:B,D1,A:A)


In article ,
mikeburg
wrote:

I have a list of figures in column A & each figure's 3 digit code in
column B.
What would be a cell formula that could be put in columns C & D to
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 user
defined function. However, if it can't be done with formulas or a
function then VBA would help a lot!

Thank you for all the help, 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


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=467735

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"mikeburg" wrote in
message ...

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


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=467735

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


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=467735

You're welcome !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--