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

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

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