SUMIF maybe?

[mriggio]

I have info as such:
Activity
Amount Description
570 Beriah
600 Matt
1000 Mike
680 Doug
680 Jared
-2850 Landview Properties (Rent/Sec)

I then have a cell where it sums up the total for each person (using SUMIF), however, for the deductions (like -2850) I need each individual's totals to be reduced by 1/5 of the actual deduction. So if each individual had a balance of $100, and a deduction of $100 was made, each individual would then have a total of $80.

Thanks everyone in advance.

 

[PaddyD]

Matt,

Sorry - I can't understand your question. In particular, I can't see how the entry with a 'deduction' relates to the other entries - how do you know that they're related? Or is there only ever one deduction & it relates to all the values? (I.E. just a ceck for a negative amount, then multiply the sumif result by 0. Is it always just 20% off? Is the amount of the deduction irrelevant?

How about posting back with a bit more info - maybe also the sumif you're using.

Paddy

 

[Aladin Akyurek]

On 2002-08-21 16:09, mriggio wrote:
I have info as such:
Activity
Amount Description
570 Beriah
600 Matt
1000 Mike
680 Doug
680 Jared
-2850 Landview Properties (Rent/Sec)

I then have a cell where it sums up the total for each person (using SUMIF), however, for the deductions (like -2850) I need each individual's totals to be reduced by 1/5 of the actual deduction. So if each individual had a balance of $100, and a deduction of $100 was made, each individual would then have a total of $80.

Thanks everyone in advance.

Can you explain the deduction bit using Matt from the list? Do the names occur just once in the 2nd column as the sample suggests?

 

[mriggio]

Yes, my description was rather vague. My apologies. The spreadsheet is being used for a budget ledger for 5 roommates, using one consolidate bank account. So, firstly, I record deposits made by each roommate. Secondly, deductions (expense, withdrawal)are recorded. As the previous activity is recorded I have a column tracking each roommates balance in the account. So I have a SUMIF for each name, which works fine adding up the deposits for each name. However, I need each recorded deduction to be divided up evenly between each of the 5 roommates. So for "Matt":
Amount Description
40 Matt Deposit
95 Matt Deposit
65 Matt Deposit
-100 Payment for Electricity

So, using a SUMIF for "Matt Deposit", I get $200, but I need only 20% of the ($100) to be deducted from the "Matt" balance.

Does this help?

For the adding of deposits I am using:
=SUMIF(B:B,"Matt",A:A)

Thanks much.

 

[Aladin Akyurek]

On 2002-08-22 09:27, mriggio wrote:
Yes, my description was rather vague. My apologies. The spreadsheet is being used for a budget ledger for 5 roommates, using one consolidate bank account. So, firstly, I record deposits made by each roommate. Secondly, deductions (expense, withdrawal)are recorded. As the previous activity is recorded I have a column tracking each roommates balance in the account. So I have a SUMIF for each name, which works fine adding up the deposits for each name. However, I need each recorded deduction to be divided up evenly between each of the 5 roommates. So for "Matt":
Amount Description
40 Matt Deposit
95 Matt Deposit
65 Matt Deposit
-100 Payment for Electricity

So, using a SUMIF for "Matt Deposit", I get $200, but I need only 20% of the ($100) to be deducted from the "Matt" balance.

Does this help?

For the adding of deposits I am using:
=SUMIF(B:B,"Matt",A:A)

Thanks much.

=SUMIF(B:B,"Matt",A:A)-20%*SUMIF(A:A,"<0")

I just assumed that you enter neg amounts in the same range as deposits.

Replace A:A and B:B by definite ranges if possible.

Is this what you wanted to do?

 

[mriggio]

Thanks Aladin. Perfect.

I just changed the "-" to "+" because the deductions are entered as negative numbers to having the "-" was actually adding the deduction.

Much, much appreciated.