# SUMIF maybe?

#### mriggio

##### Board Regular
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.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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?

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.

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?

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.

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?

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

Much, much appreciated.

Replies
3
Views
209
Replies
5
Views
244
Replies
1
Views
242
Replies
1
Views
186
Replies
1
Views
495

1,203,323
Messages
6,054,728
Members
444,747
Latest member
Jaborsum

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back