Microsoft Excel - I need help with a cell formula?

Question

I have spreadsheet with receipt dates in column A and dollar amounts of each receipt in column B. I need to total the dollar amounts based on monthly totals from the various receipt dates, so that I know how much was spent each month. How do I construct a formula that will search all of column A for receipts in a particular month and then grab the dollar amounts for those receipts, and present them as a total spent for that month?

Answer 1

We can use an array formula for this one.

In a separate place (column) type the no of the months starting from 1 thru 12 .(Eg Jan =1, Dec=12)

I think your dates are in a format that excel can understand.
Say your cells (1 , 2 ... 12) are in the range A1:A12 and your receipts dates - D1:D500 - Your amounts - E1:E500

Type the below formula in B1
= SUM (IF (MONTH ($D$1:$D$500)=A1, $E$1:$E$500, 0))

Note : -When you enter the formula, don't just press "Enter" only. You have to press all Shift+Ctrl+Enter together. Then it will have 2 additional brackets around your formula.

Then copy it down

Good Luck

Answer 2

Hey dont worry its easy. In the cell where you get the total number of hours that you have worked for the day, the formula might have been modified(which is adding the lunch hour also to the actual number of working hours). You need to select the cell wher you get total hours, check the formula. check what is the cell number for the lunch hour (example A6, B12, ... ); now check this cell address in the formula of the total hours worked field & just delete this address only. This should help you. But sometimes, what heppens is though you will be edit one row(or for one day) in this way; the next day you come maybe you will face the same issue. In that case, the formula has actually been applied to all the cells under the total hours worked column. You just need to check in the main cell and drag it downwards through the entire column for this field. Hope you understood this. Its easy to do it; hard to explain ! Best of luck!

Answer 3

Sounds like you need to do some research on Pivot Tables in Excel, which let you group (add/count etc) data from one column according to the values in another.

More flexible than formulae though you could research the sumif()

Answer 4

A simple formula would be one using the SUMIF() function. Unfortunately, the SUMIF() function only takes one condition, so you have to apply it twice.

For example for the month of July 2007, in cell C1 use the formula:

"=SUMIF(A:A,">2007/06/30",B:B) - SUMIF(A:A,">2007/07/31",B:B)"

This takes all values AFTER 30 June, 2007 and sums them, then subtracts all values AFTER 31 July, 2007. It assumes your date format is YYYY/MM/DD, but you can substitute whatever format you actually use.

Answer 5

The lookup function might also be helpful- you specify what you want it to look for, where it should look and what cells it should return from if found. For example, look for December in Column A, if found, return the corresponding value from Column B

Answer 6

This needs one correction.

This will pull the amount from col A2 to B2.
Note carefully "NOW" in parenthesis is followed by "()", then enclosed ( ). The "", double parenthesis enters a void if the row is not the current month.
Sum col B at bottom.

=IF(MONTH(A2)=MONTH(NOW()),B2, "")
When the next month arrives, your last month will change to the current month in NOW.

Change the formula.
=IF(MONTH(A2)=8,B2,"")
You will have to change the month from 8 to 9, etc in the next col. for each month.

You will have to use a new col. for each additional month.
Be sure cell reference is correct. Make it absolute ref.

Sorry about that.

Answer 7

use this formula:
don't forget to put equal sign before you put the formula on the box you wish to put it.
the row number and column letter is like a legend

example:
=A1+B1
or
=B1+....B25
or just use the formula bar.

Answer 8

One way that you can do it is to type:
=Sum(A2:A150)

Beginning column, row #, Ending column, row #