Formula Help

[johndowd]

I'm trying to figure out how to create a simple formula that will do the following:Calculate the total amount I've paid to suppliers vs what I still owe suppliers.

In column A I list various suppliers, each of them twice - e.g. A1=Stone, A2=Stone.

In column B I have the words "Invoiced" and "Paid" - e.g. B1=Invoiced, B2 = Paid. Assume I have 50 suppliers, and that A3:A100 are filled out similarly. B3:B100 the same.

In C1 I'll put the amount of the invoice, and in C2 I'll put the amount I've paid.
C3:C100 the same for the other 49 suppliers.

What I want to do is to sum all the Invoiced cells, subtract the Paid cells, and show the difference still owing (in total).

Any ideas? Did I state the question clearly? Thanks!

[Aladin Akyurek]

On 2002-05-15 18:34, johndowd wrote:
I'm trying to figure out how to create a simple formula that will do the following:Calculate the total amount I've paid to suppliers vs what I still owe suppliers.

In column A I list various suppliers, each of them twice - e.g. A1=Stone, A2=Stone.

In column B I have the words "Invoiced" and "Paid" - e.g. B1=Invoiced, B2 = Paid. Assume I have 50 suppliers, and that A3:A100 are filled out similarly. B3:B100 the same.

In C1 I'll put the amount of the invoice, and in C2 I'll put the amount I've paid.
C3:C100 the same for the other 49 suppliers.

What I want to do is to sum all the Invoiced cells, subtract the Paid cells, and show the difference still owing (in total).

Any ideas? Did I state the question clearly? Thanks!

=SUMIF(B2:B100,"Invoiced",C2:C100)-SUMIF(B2:B100,"paid",C2:C100)

[Yogi Anand]

On 2002-05-15 18:34, johndowd wrote:
I'm trying to figure out how to create a simple formula that will do the following:Calculate the total amount I've paid to suppliers vs what I still owe suppliers.

In column A I list various suppliers, each of them twice - e.g. A1=Stone, A2=Stone.

In column B I have the words "Invoiced" and "Paid" - e.g. B1=Invoiced, B2 = Paid. Assume I have 50 suppliers, and that A3:A100 are filled out similarly. B3:B100 the same.

In C1 I'll put the amount of the invoice, and in C2 I'll put the amount I've paid.
C3:C100 the same for the other 49 suppliers.

What I want to do is to sum all the Invoiced cells, subtract the Paid cells, and show the difference still owing (in total).

Any ideas? Did I state the question clearly? Thanks!

Use the following array formula to get the balance owed:

{=SUM(C1:C100)-2*SUM(IF(MOD(ROW(1:100),2)=0,C1:C100,0))}

Please post back if it works for you .. otherwise explain a little further and let us take it from there.

Regards!

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 17:09 ]

[johndowd]

Okay folks, thanks so much for the quick response. Aladin's formula worked best, as it correctly gave me the difference between the sum of the "invoiced" and "paid". However, when I tried to use it on a secondary spreadsheet, it wouldn't work. By secondary sheet I mean the 2nd tab of the existing workbook - Excel default gives you three per workbook.

Yogi's formula worked, but only gave me the sum of both the "invoiced" and "paid", not the difference.

Anyway, again many thanks. I've found other uses for Aladin's formula already! Any help on the new problem is appreciated!

[ This Message was edited by: johndowd on 2002-05-16 16:15 ]

[Yogi Anand]

On 2002-05-16 16:14, johndowd wrote:
Okay folks, thanks so much for the quick response. Aladin's formula worked best, as it correctly gave me the difference between the sum of the "invoiced" and "paid". However, when I tried to use it on a secondary spreadsheet, it wouldn't work. By secondary sheet I mean the 2nd tab of the existing workbook - Excel default gives you three per workbook.

Yogi's formula worked, but only gave me the sum of both the "invoiced" and "paid", not the difference.

Anyway, again many thanks. I've found other uses for Aladin's formula already! Any help on the new problem is appreciated!

[ This Message was edited by: johndowd on 2002-05-16 16:15 ]

Hi johndowd:
Well first, I did check my formula again -- it works for me. You may want to make sure every thing is entered correctly.
Now back to the first part of your statement, that the formulas provided to you do not work on the second sheet. Why do you say they do not work on the second sheet -- both formulas, Aladin's as well as mine do not have sheet specific information coded in the formulas. Are you using the same cell references? Is your data range the same as it was in the first sheet? You may want to double check these things. If you want you can email your worksheet to me -- I will be glad to look at it.

Regards!