Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This formula needs to be more compact.
Hi all, I have another formula that I would like some assistance with. Currently I use about 14 income accounts, all of which have a unique 2 letter code to define them. I have a spreadsheet that's about 30 lines deep per day with the different incomes listed, and a set of corresponding formulas to pick out each one by name underneath. What I have does work, but it's very large & very cumbersome to edit. I was wondering if there was a way to make the formula more user friendly for times when I need to make more income lines. I just used a basic =IF formula and repeated it... =IF(C5 ="IN",B5,0)+IF(C6 ="IN",B6,0)+IF(C& ="IN",B6,0) ad nauseum all the way through each of 30 lines, and each of 14 different 2 letter codes. As I said, it does work, but it's a royal pain if you need to make changes etc. Any ideas? Thanks -- britgirl ------------------------------------------------------------------------ britgirl's Profile: http://www.excelforum.com/member.php...o&userid=29957 View this thread: http://www.excelforum.com/showthread...hreadid=496843 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This formula needs to be more compact.
Hi,
Why not use SUMIF For eg. =SUMIF(C$5:C$100,"IN",B$5:B$100) You dont even have to specify "IN" within quotes if you have that in a separate cell. If the code "IN" is specified in cell say D5, then you can use =SUMIF(C$5:C$100,D5,B$5:B$100) This is a dynamic formula so if the other codes are in cells D6,D7 etc, you can just copy this down and it would automatically compute the sum for other codes as well. Regards Govind. britgirl wrote: Hi all, I have another formula that I would like some assistance with. Currently I use about 14 income accounts, all of which have a unique 2 letter code to define them. I have a spreadsheet that's about 30 lines deep per day with the different incomes listed, and a set of corresponding formulas to pick out each one by name underneath. What I have does work, but it's very large & very cumbersome to edit. I was wondering if there was a way to make the formula more user friendly for times when I need to make more income lines. I just used a basic =IF formula and repeated it... =IF(C5 ="IN",B5,0)+IF(C6 ="IN",B6,0)+IF(C& ="IN",B6,0) ad nauseum all the way through each of 30 lines, and each of 14 different 2 letter codes. As I said, it does work, but it's a royal pain if you need to make changes etc. Any ideas? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This formula needs to be more compact.
Brilliant, thanks. :) -- britgirl ------------------------------------------------------------------------ britgirl's Profile: http://www.excelforum.com/member.php...o&userid=29957 View this thread: http://www.excelforum.com/showthread...hreadid=496843 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |