Formula for SUM of based on cell value

[LaurDor88]

Hi all,

I really need help with calculating the SUM of 3 different values.

I've got a spreadsheet for my finances, and my bills/outgoings.

In Column A is simply the name of the bill i.e. debtor/company
In Column B I have the date that the bill is due.
In Column C I have the amount of the bill i.e. £100 or whatever it may be.
In Column D I have whether or not the bill has been paid (or not yet due) - I have used conditional formatting on this so it highlights red/yellow/green accordingly.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Bill[/TD]
[TD]Due Date[/TD]
[TD]Amount[/TD]
[TD]Paid?[/TD]
[TD][/TD]
[TD]Sums[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD]1/1[/TD]
[TD]100[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Paid[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]Tax[/TD]
[TD]8/2[/TD]
[TD]30[/TD]
[TD]Not Yet Due[/TD]
[TD][/TD]
[TD]Yet to be paid[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]Phone[/TD]
[TD]2/2[/TD]
[TD]30[/TD]
[TD]No[/TD]
[TD][/TD]
[TD]Unlawfully Outstanding[/TD]
[TD]=[/TD]
[/TR]
</tbody>[/TABLE]


Now, I need to calculate based on whether or not it has been paid. I want three SUMs...
1. Amount Paid (i.e. Column D = "Yes")
2. Amount Yet To Be Paid (i.e. Column D = "Not Yet Due")
3. Amount Unlawfully Outstanding (i.e. Column D = "No")

Is there any way of doing this?

Thank you all.

 

[AlanY]

Hi all,

I really need help with calculating the SUM of 3 different values.

I've got a spreadsheet for my finances, and my bills/outgoings.

In Column A is simply the name of the bill i.e. debtor/company
In Column B I have the date that the bill is due.
In Column C I have the amount of the bill i.e. £100 or whatever it may be.
In Column D I have whether or not the bill has been paid (or not yet due) - I have used conditional formatting on this so it highlights red/yellow/green accordingly.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Bill[/TD]
[TD]Due Date[/TD]
[TD]Amount[/TD]
[TD]Paid?[/TD]
[TD][/TD]
[TD]Sums[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD]1/1[/TD]
[TD]100[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Paid[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]Tax[/TD]
[TD]8/2[/TD]
[TD]30[/TD]
[TD]Not Yet Due[/TD]
[TD][/TD]
[TD]Yet to be paid[/TD]
[TD]=[/TD]
[/TR]
[TR]
[TD]Phone[/TD]
[TD]2/2[/TD]
[TD]30[/TD]
[TD]No[/TD]
[TD][/TD]
[TD]Unlawfully Outstanding[/TD]
[TD]=[/TD]
[/TR]
</tbody>[/TABLE]


Now, I need to calculate based on whether or not it has been paid. I want three SUMs...
1. Amount Paid (i.e. Column D = "Yes")
2. Amount Yet To Be Paid (i.e. Column D = "Not Yet Due")
3. Amount Unlawfully Outstanding (i.e. Column D = "No")

Is there any way of doing this?

Thank you all.

a sumif() solution;

for the paid =SUMIF(D2:D4,"=yes",C2:C4)
similarly, replace "=yes" with "=Not Yet Due" and "=No" for the others

 

[SteveO59L]

=SUMIF(D:D,"Yes",C:C)

 

[Caribeiro77]

Welcome to the board....
Assuming that your SUMS column is E and your criteria are starting on E2, on E2 try:

E2 =SUMIF($D$1:$D$3,"Yes",$C$1:$C$3)
E3 =SUMIF($D$1:$D$3,"Not yet due",$C$1:$C$3)
E4 =SUMIF($D$1:$D$3,"No",$C$1:$C$3)

 

[LaurDor88]

Thanks so much! I don't know why I didn't think of that!