Page 1 of 1
| EXCEL formula query 12/12/2010 at 14:25 #2108 | |
Peter Bennet
5402 posts |
I'm trying to write a formula to extract information form a spreadsheet for a graph and I'm a little stuck so any pointers appreciated. I have a column of numbers A and in column B I have a date and what I want to be able to do is summate all the values in A that are conditional on a test on a date range in B. I know I can simply do this by filter but as as want to do 12 tests (one for each month) and subsequently for future years and retain the data for the graph that's too simplistic. Essentially what I will have is a table of months where for each Month I have the formula (Sum the value in "A" only where the date in B=Month). I know that I can do it "long-hand" with a big 2D [hidden] grid of months with each cell copying its respective A value using a [IF Month= then A ] test and summing the totals of each column but I would like a tidier method if possible. Peter I identify as half man half biscuit - crumbs! Log in to reply |
| EXCEL formula query 12/12/2010 at 14:25 #12876 | |
|
Peter Bennet
5402 posts |
I'm trying to write a formula to extract information form a spreadsheet for a graph and I'm a little stuck so any pointers appreciated. I have a column of numbers A and in column B I have a date and what I want to be able to do is summate all the values in A that are conditional on a test on a date range in B. I know I can simply do this by filter but as as want to do 12 tests (one for each month) and subsequently for future years and retain the data for the graph that's too simplistic. Essentially what I will have is a table of months where for each Month I have the formula (Sum the value in "A" only where the date in B=Month). I know that I can do it "long-hand" with a big 2D [hidden] grid of months with each cell copying its respective A value using a [IF Month= then A ] test and summing the totals of each column but I would like a tidier method if possible. Peter I identify as half man half biscuit - crumbs! Log in to reply |
| EXCEL formula query 12/12/2010 at 15:02 #12877 | |
postal
5264 posts |
Peter You need the SUMIF function. I've attached a little file that has a random set of numbers and dates over a 12 month period. These are totalled by month in a little block to the right of the data and then a graph created from the block of totals. Let me know if that is the sort of thing you want or what you would like to change to fit your needs. The system won't allow me to upload a .xls file so it is zipped up even thought the size is not a problem. JG Montly Totals File “In life, there is always someone out there, who won’t like you, for whatever reason, don’t let the insecurities in their lives affect yours.” – Rashida Rowe Log in to reply |
| EXCEL formula query 12/12/2010 at 15:48 #12878 | |
|
Peter Bennet
5402 posts |
Brilliant. that's exactly what I wanted- have modified it to include Year as a sum of M*10000+Y and that all works. Many thanks Peter xls added to allowable extensions I identify as half man half biscuit - crumbs! Log in to reply |
| EXCEL formula query 13/12/2010 at 10:36 #12882 | |
andyb0607
260 posts |
Recently been trying to work out something similar myself. So thanks Peter for asking the question and thanks John, you've helped me out as well!! Andy Log in to reply |
| EXCEL formula query 13/12/2010 at 14:56 #12887 | |
|
postal
5264 posts |
Glad to help.
“In life, there is always someone out there, who won’t like you, for whatever reason, don’t let the insecurities in their lives affect yours.” – Rashida Rowe Log in to reply |
| EXCEL formula query 18/12/2010 at 15:01 #12934 | |
wulf
27 posts |
This is something a pivot table can do in Excel, without so much faffing. Since pivot tables are my favourite thing in Excel, I thought I would demonstrate  1. Insert column headings in the list (needed for the pivot) 2. Select the list and do Insert / Pivot Table 3. Drag the "date" field to the row labels part of the table 4. Drag the "Number" field to the values area 5. Right-click in the date column in the pivot table and select "Group", then select "months" I have updated the spreadsheet and attached it here Log in to reply |
| EXCEL formula query 18/12/2010 at 15:46 #12935 | |
|
postal
5264 posts |
Wulf Looks very nice. No doubt due to the way that I am a creature of habit (and still locked into the way of doing things I learnt with Supercalc in the pre-Windows days of DOS and command lines) I never think of pivot tables when I am playing with Excel. Having said that, I personally find pivot tables such a hassle and so difficult to manipulate when you decide you want to change things that I never use them as the function of choice. However, if it is easier for Peter or Andy to use the pivot table, then great. Incidentally, I haven't tried, but according to Peter's posting earlier in this thread, it should now be possible to upload .xls files direct rather than having to zip them. JG “In life, there is always someone out there, who won’t like you, for whatever reason, don’t let the insecurities in their lives affect yours.” – Rashida Rowe Log in to reply |
| EXCEL formula query 18/12/2010 at 17:53 #12937 | |
|
Peter Bennet
5402 posts |
I did think of a pivot table but I can never remember how they work. Did an advance Excel course many years ago for work but that part I never really used and hence the memory fades. Thanks I identify as half man half biscuit - crumbs! Log in to reply |