Microsoft Excel Spreadsheet - HELP

paddyrg

You can also do some very cool stuff with Date Filters in Pivot Tables.

Wurst

Example solution with simple pivot table using suggestions from previous posters. No fancy stuff required. Might help someone...

h**ps://www.mediafire.com/?975syvba1wgsc3o

Heedtheadvice

Was just going to post but have been beaten too it...but what the.....

PIVOT TABLE is the best and easiest way to summarise your data, does not involve formulae, updatable, can be customised to change headings formats and copes well with dates or days or months or years and can sum, count average etc.etc.

The must be first port of call as it is designed exactly for your purpose!

Stompa

If you want to go down the formulae route, then one simple option would be to use SUMIFS(), so something like:

=SUMIFS(C1:C10,B1:B10,">="&DATE(YEAR(D1),MONTH(D1),1),B1:B10,"<"&DATE(YEAR(D1),MONTH(D1)+1,1))

where your dates are in B1:B10, your values in C1:C10, and D1 contains a date with the relevant month/year (the day is ignored) that you want to sum.

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.

Hello again,

my spreadsheet has been working great with the formula you provided me but I've come up with a problem.

I have a 'Debit Amount' and 'Credit Amount' headings and I had a credit note invoice today and when I came to inputting it on the spreadsheet that amount was added and not deducted for this month.

For example, to date I've spent £400 for July and I had this credit note today for £100 but when I came to inputting the £100 as credit it added a £100 to the £400 so it shows that I've spent £500 not £300 as it should be.

Are you able to save me again as I've tried to play about with the formula but I've had no luck???

bod1467

We'll need to see your formula, highlighting which cells/column is the Credits one.

Anything in the Credits column should be subtracted not added. OR ... enter it into that column as a negative value like -£100.

NathanMorgan

bod1467 wrote: »

We'll need to see your formula, highlighting which cells/column is the Credits one.

Anything in the Credits column should be subtracted not added. OR ... enter it into that column as a negative value like -£100.

Here's the current formula which only deducts from a starting balance but I need it to add credit amounts as well.

=SUM(D4:E1003*IF(MONTH(A4:1003)=MONTH("1/"&G12&"/15"),1,0))

***E1003 is the credit cells***

Cornucopia

So the debit cells are a separate column?

I think the most straightforward way is to use the formula you have twice, making it into one big forumla - one part for all the credits, and the other for all the debits, and deduct one from the other.

You can avoid problems with forum smiley codes by using the code tag. It's the "#" symbol, second from the right in the toolbar.

Your existing formula appears like this:

=SUM(D4:E1003*IF(MONTH(A4:A1003)=MONTH("1/"&G12&"/15"),1,0))

Personally, I'm not a big fan of array formulae, and I find the above less intuitive than the SUMIFS version provided earlier. But that could be because I'm an old school programmer.

bingo_bango

This should work, but I haven't tested it. It assumes that all debits are in Column D, and all credits in Column E.

=(SUM(D4:D1003*IF(MONTH(A4:A1003)=MONTH("1/"&G12&"/15"),1,0)))-(SUM(E4:E1003*IF(MONTH(A4:A1003)=MONTH("1/"&G12&"/15"),1,0)))

NathanMorgan

bingo_bango wrote: »

This should work, but I haven't tested it. It assumes that all debits are in Column D, and all credits in Column E.

=(SUM(D4:D1003*IF(MONTH(A4:A1003)=MONTH("1/"&G12&"/15"),1,0)))-(SUM(E4:E1003*IF(MONTH(A4:A1003)=MONTH("1/"&G12&"/15"),1,0)))

This formula worked.

Thank you for your help its much appreciated.