This formula needs to be more compact.

[britgirl]

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

[Govind]

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
>
>

[britgirl]

Brilliant, thanks.