If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
You're welcome !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 11:26 PM |
Export to RTF very slow when code is present in Access report. | [email protected] | Setting Up & Running Reports | 11 | September 14th, 2004 08:17 PM |
Hi! Urgent pls: Protecting sheets so macros/buttons still work? | StargateFanFromWork | General Discussion | 7 | July 3rd, 2004 02:25 PM |
Runtime Generating Formula with VBA Code | bm | Worksheet Functions | 2 | May 11th, 2004 06:58 PM |
Placing a formula into a cell through VBA code | Michelle Hillard | Worksheet Functions | 1 | December 24th, 2003 11:41 PM |