How do I make a custom formula for Excel?

  • Thread starter Thread starter infamouswb
  • Start date Start date
I

infamouswb

I use excel for my checkbook register and I have a budget that "runs" beside
it to keep a monthly track of everything in the same worksheet. I want to
write a formula that would take, for instance, my gasoline purchases from the
checkbook, then place and continue to add up the monthly gasoline purchases
in the budget column. I know very little about formulas. Right now, I
manually add up all the rows that say Gas for the month and place them into
the budget line. I appreciate your assistance.
 
There are a variety of ways to create cums for budgets, depending on how you
have your workbook setup.

From your example, I would suggest the SUMIF function. Excel's help can
provide more insight, but something like

=SUMIF(A:A,"Gas",B:B)
Would give you a sum of all numbers in the B column where you had the word
"Gas" in the A column.

I'd also suggest looking up in the help file about freezing cells (so you
can always see budget line).
 
Modify to suit. Assumes valid dates in col A, categories like gas in col c
and the amounts in d
=SUMPRODUCT((MONTH(A7:A500)=ROW(A1))*(C7:C500="gas")*D7:D500)
I suggest using a credit card with a 1% or better rebate.
 
You will want to make the range absolute before copying down
=SUMPRODUCT((MONTH($A$7:$A$500)=ROW(A1))*($C$7:$C$500="gas")*$D$7:$D$500)
 
Back
Top