Microsoft Excel Spreadsheet - HELP

NathanMorgan

I've created a budget spreadsheet for work with the following headings :

Date Name Invoice No. Debit Amount Credit Amount


The date heading is in the dd/mm/yyyy format. I want to create a formula that will calculate the amount I've spent for each month, so the headings for these are :

January
February
etc.

I know that the formula is either SUMIF or SUMPRODUCT but I want to translate the dd/mm/yyyy so that it links up to a specific month (if that makes sense.

HELP!!!

MothballsWallet

Which version of Excel do you use?

I'd also try to avoid SUMPRODUCT if you can, it works, but it can be harder to work out what you were trying to do

You can do it with SUMIF, and I'd recommend using a separate tab for your summary data, say "Summary", and rename your data tab to something like "Transactions".

Type the following formula into cell F2 on the "Transactions" tab:

=MONTH(A2)

Now copy this formula down column F so you get a month number for all your transactions.

Go back to the "Summary" tab and set it up as follows:

1. Column A: Month
2. Column B: Total Debits

In cell A2, start filling in the month numbers from 1 and change the format to "mmm", which will give you the month name in short format, e.g., "Jan".

In cell B2, type the following formula:

=SUMIF(Transactions!F:F,A2,Transactions!C:C)

Now copy and paste this formula down to December's row.

That should give you what you want to do: if you get any "VALUE" errors, change the SUMIF formula to reference the rows you used rather than the whole columns.

Personally, I'd use named ranges because you can set them up to an initial cells area and then change their definitions later without having to alter your formulas every time.

NathanMorgan

im using Excel 2010.

When I do the SUMIF formula it doesn't recognise the dd/mm/yyyy format as the formula is in the "February" heading.

For example,

Date. Name. Debit Amount

03/02/2015 McDonald's. 10.00
06/02/2015. KFC. 5.00

February Total (SUMIF formula?)

MothballsWallet

I'd add an extra column with a formula to extract the month from the date column, which you'd write as follows:

=MONTH(A)

This returns a number in the range 1-12 for the month, with 1 being January, up to 12 for December.

You then use this new column in your SUMIF - remember SUMIF works with {test range}, {condition}, {sum range}, so you'd use:

=SUMIF(F:F,{month cell},C:C)

Where {month cell} is where you've got the month (again, extract that with the MONTH function.

I'd type the dates as 1/2/15 in the cell and then use the "mmm" cell format to display the month only, then put "Total" in the next column.

You can then use MONTH(A) as the condition in the SUMIF formula.

If you're still not sure (and I may not be explaining it very well), can you upload the spreadsheet somewhere I can access it and I'll do it in situ for you?

MothballsWallet

Actually, the example below might help explain it a bit better (I'm afraid MSE's post editor has messed up the formatting for me, so I hope it's still understandable):

A B C D
1 Date. Name. Debit Amount. Month.
2 03/02/2015 McDonald's. 10.00 =MONTH(A2)
3 06/02/2015 KFC. 5.00 =MONTH(A3)
01/02/2015* Total =SUMIF(D2:D3,MONTH(A4),C2:C3)

* Remember to format this as "mmmm" to display the full month name in this cell.

Also, MSE has changed into a smiley...

Personally, I'd create a new tab and put my monthly summary data on there to keep it a little clearer, but that's just me

NathanMorgan

im actually looking for a way in doing this without adding a 'Month' as it looks quiet silly with the date with the format dd/mm/yyyy as that's already got the month in it.

I can see how it will be easier to get my desired result with the 'Month' tab but im sure there's a way in doing it without it.

This video shows what im after and wondering how I would use the formula in my spreadsheet.

https://youtu.be/x_e55K_A-u4

MothballsWallet

You may be able to use MONTH in the first argument of the SUMIF function, but I've never tried it myself.

You can use the SUM function with named ranges - that way, you can use arrays in the formula.

Going from my example earlier, you could use (and once again MSE changes the colon-A to an angel smiley):

=SUM((A2:A3=MONTH(A4))*C2:C3)

You need to press Alt+Enter to save the formula: Excel puts array formulas in { and } to distinguish them from normal formulas.

You can use the Name Manager on the Formulas tab on the ribbon to create names for your data cells, and then use those names in your formulas, which will make them easier to follow, e.g.

Define A2:A3 as Dates and C2:C3 as Debits, you could change the formula to:

=SUM((Dates=MONTH(A4))*Debits)

Remember to press Alt+Enter to save it.

I'd try to avoid SUMPRODUCT if you can because its syntax is more complex and you're bound to forget what it's doing for you if you need to change it later

thenudeone

It's possible without adding an extra column by using the SUM function as an array function.

To show how, create a table in cells b1 - c6 of a new sheet, as follows:
Date Value
01/01/2015 100
15/01/2015 200
01/02/2015 350
15/02/2015 400
01/10/2015 500

Then in cell b9 type "February"
In cell c9 type =SUM(C2:C6*IF(MONTH(B2:B6)=MONTH("1/"&B9&"/15"),1,0)) but instead of pressing enter, press CTRL-SHIFT-ENTER. .

You will now get the correct answer of 750.

The whole formula now has curly brackets round it, showing that it is now an array formula. Each function which normally only allows a single piece of data input can accept a range of cells when used in an array function.

Basically Excel will work through each row in turn (from 2 to 6) and adding the results from each calculation to a running total, the final result of which is displayed.

In its simplest form, an array formula could be the equivalent of the sumproduct function (before sumproduct was invented), but it is possible to nest multiple formulas within it as in this example.

In this example it's very difficult to see what is going on because it's necessary to convert "February" into a date (1/2/15) in order to extract a month number (2) using the MONTH function, which can then be compared with the month number of each of the sets of data in the table.

If you ever edit or copy the formula, you will need to CTRL-SHIFT-ENTER again.

NathanMorgan

thenudeone wrote: »

It's possible without adding an extra column by using the SUM function as an array function.

To show how, create a table in cells b1 - c6 of a new sheet, as follows:
Date Value
01/01/2015 100
15/01/2015 200
01/02/2015 350
15/02/2015 400
01/10/2015 500

Then in cell b9 type "February"
In cell c9 type =SUM(C2:C6*IF(MONTH(B2:B6)=MONTH("1/"&B9&"/15"),1,0)) but instead of pressing enter, press CTRL-SHIFT-ENTER. .

You will now get the correct answer of 750.

The whole formula now has curly brackets round it, showing that it is now an array formula. Each function which normally only allows a single piece of data input can accept a range of cells when used in an array function.

Basically Excel will work through each row in turn (from 2 to 6) and adding the results from each calculation to a running total, the final result of which is displayed.

In its simplest form, an array formula could be the equivalent of the sumproduct function (before sumproduct was invented), but it is possible to nest multiple formulas within it as in this example.

In this example it's very difficult to see what is going on because it's necessary to convert "February" into a date (1/2/15) in order to extract a month number (2) using the MONTH function, which can then be compared with the month number of each of the sets of data in the table.

If you ever edit or copy the formula, you will need to CTRL-SHIFT-ENTER again.

thanks for this, its exactly what im looking for.

Could you explain what this part of the formula is as im not quiet sure what its function is - MONTH("1/"&B9&"/15"),1,0))

thenudeone

It's the best way I can think of to convert the text "February" into the number 2 representing the second month of the year, so that it can be compared to the month numbers of the source data.

The bit inside the inner brackets converts "February" into "1/February/2015" and the outer bit uses the MONTH function to convert "1/February/2015" into the number 2

stevemcol

Good solution above but remember additional "workings out" columns can alway be hidden.