Help with Function

robbiev

New Member
Joined
Sep 9, 2002
Messages
38
Hey Everyone, he's my snafu:

I have created a simple sheet for balancing my checkbook. Example Below:

Date #/Type Payee Amount New Bal.
7/31 Interest 0.16 $3.14
7/31 Deposit 525.47 $528.61
8/3 Trans Chk2Clb (25.00) $503.61
8/4 ACH GSMR (75.00) $428.61
8/5 ATM Walpole (40.00) $388.61
8/5 ATM Fee (0.75) $387.86

I would like to create a formula that would go through the "Payee" column, find all cells containing the value "Fee", then add up the values in the cells next to it, putting the total in another cell. Similarily, I would like to create 2 formulas that would go through all the values in the "Amount" column, totaling all the positive numbers, and then totaling the negative numbers in separate cells.
I'm trying to recreate what a bank statment would tell me. Any suggestions?

Thanks! :) Robbie
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi Robbie - welcome to the board.

The function you need is sumif() - check it out in the help files & see below for examples:
Book7
ABCDE
1Date#/TypePayeeAmountNewBal.
2Jul-31Interest0.16$3.14
3Jul-31Deposit525.47$528.61
48-MarTransChk2Clb-25
58-AprACHGSMR-75$428.61
68-MayATMWalpole-40$388.61
78-MayATMFee-0.75$387.86
8
9
10Fees:387.86
11Positive525.63
12Negative:-140.75
13
Sheet3


Post back if you need more,

Paddy
 
Upvote 0
OK...Thanks! The pos and neg formula's worked...but the "fee" one didn't. If you went by the example there, the fee total would only be 0.75, because there was only one fee, not the amount shown..., but i'll still look at the help file about it!
:) robbie
 
Upvote 0
robbie,

my error - the fee sumif was just summing the wrong column. Try:

=SUMIF(C2:C7,"=Fee",D2:D7)

Paddy
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top