Calculating values in two columns based on a variable

[Alex]

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

 

[T. Valko]

For the total in E11:

=E2+SUM(D39)-SUM(C3:C9)

For the "total cleared" in E12:

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

Biff

 

[Alex]

For the total in E11:

=E2+SUM(D39)-SUM(C3:C9)

For the "total cleared" in E12:

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

Biff

Hi Biff,

Worked perfectly... Thanks

Alex

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Hi Biff,

Worked perfectly... Thanks

Alex