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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
 

robbiev

New Member
Joined
Sep 9, 2002
Messages
38
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
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
robbie,

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

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

Paddy
 

Watch MrExcel Video

Forum statistics

Threads
1,122,957
Messages
5,599,046
Members
414,281
Latest member
Engjamal2021

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
Top