Calculating values in two columns based on a variable
 

Hi Everyone,

I'm setting up a spreadsheet to act similarly as a check register, and
I'm not sure how to calculate the running totals based on whether the
items have cleared the bank or not. Here's a sample spreadsheet
showing what I'm trying to do (probably easier then trying to describe
it here):
http://www.hotlug.org/docs/example.xls

Basically I have one field for Deposits and one for Withdrawals, plus a
field to label each as Pending or Cleared, depending on whether it's
shown up online or not. What I want is a field at the bottom of the
spreadsheet to show Total items then Total Cleared items. I've been
trying to figure out how to do this with the SUMPRODUCT formula, but
since I need to add the Withdrawal field and subtract the Deposit field
from the starting balance, I'm not sure if this function will work or
if so how to do it.

If I can calculate all the Cleared items at a glance, I can compare
this quickly to what the bank shows to verify everything is balanced
out properly. Thanks for any suggestions with this.

Alex

Calculating values in two columns based on a variable
 

For the total in E11:

=E2+SUM(D3:D9)-SUM(C3:C9)

For the "total cleared" in E12:

=(F2="cleared")*E2+SUMIF(F3:F9,"cleared",D3:D9)-SUMIF(F3:F9,"cleared",C3:C9)

Biff

"Alex" wrote in message
ps.com...
Hi Everyone,

I'm setting up a spreadsheet to act similarly as a check register, and
I'm not sure how to calculate the running totals based on whether the
items have cleared the bank or not. Here's a sample spreadsheet
showing what I'm trying to do (probably easier then trying to describe
it here):
http://www.hotlug.org/docs/example.xls

Basically I have one field for Deposits and one for Withdrawals, plus a
field to label each as Pending or Cleared, depending on whether it's
shown up online or not. What I want is a field at the bottom of the
spreadsheet to show Total items then Total Cleared items. I've been
trying to figure out how to do this with the SUMPRODUCT formula, but
since I need to add the Withdrawal field and subtract the Deposit field
from the starting balance, I'm not sure if this function will work or
if so how to do it.

If I can calculate all the Cleared items at a glance, I can compare
this quickly to what the bank shows to verify everything is balanced
out properly. Thanks for any suggestions with this.

Alex

Calculating values in two columns based on a variable
 

T. Valko wrote:
For the total in E11:

=E2+SUM(D3:D9)-SUM(C3:C9)

For the "total cleared" in E12:

=(F2="cleared")*E2+SUMIF(F3:F9,"cleared",D3:D9)-SUMIF(F3:F9,"cleared",C3:C9)

Biff


Hi Biff,

Worked perfectly... Thanks :)

Alex

Calculating values in two columns based on a variable
 

You're welcome. Thanks for the feedback!

Biff

"Alex" wrote in message
ps.com...

T. Valko wrote:
For the total in E11:

=E2+SUM(D3:D9)-SUM(C3:C9)

For the "total cleared" in E12:

=(F2="cleared")*E2+SUMIF(F3:F9,"cleared",D3:D9)-SUMIF(F3:F9,"cleared",C3:C9)

Biff


Hi Biff,

Worked perfectly... Thanks :)

Alex