Hi LubberLou,

Based on your example, I created the following named ranges:

Paid_By = column B
Owed_By = "KT" in D1, "JB" in E1
Owed_To = "JB" in D2, "KT" in E2
Cost = column C
Amount = column A

The following formula works for me:

=IF(AND(Paid_By=Owed_By,Cost="paid"),-Amount,0)+IF(AND(Paid_By=Owed_To,Cost="shared"),Am ount/2,0)+IF(AND(Paid_By=Owed_To,Cost="non-shared"),Amount,0)

Hope this helps.

"LubberLou" wrote:


I have a sheet of 5 columns. I'm trying to make a spreadsheet of costs
between two people to show who pays for what and what each person owes the
other. The columns look like this:

A - a varying amount (in dollars)
B - who the amount was paid by (either KT or JB)
C - whether or not the cost is a shared, non-shared, or paid cost (either
"s", "n", or "p" text-values)
D - what KT owes JB
E - what JB owes KT

Here's the thing. I want the amounts in columns D and E to change based on
the various combinations of B and C together. So here are the formulas:

If B=KT and C=S, then D=$0 and E=A/2
If B=KT and C=N, then D=$0 and E=A
If B=KT and C=P, then D=$-A and E=$0

If B=JB and C=S, then D=A/2 and E=$0
If B=JB and C=N, then D=A and E=$0
If B=JB and C=P, then D=$0 and E=$-A

I've tried using many, many different if formulas and none of them seem to
work correctly. Please help! Thanks so much!