Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need totals of values that fall within a given year
I've tried SUMIF, and SUMPRODUCT. . .Trying to total values that
occurred within a given year: We have a column of historical costs, and a corresponding item purchase date. The historical costs to total are in a range such as V5:V2050. (Some cells may be blank) The dates those values were purchased are in a range such as Z5:Z2050 (formatted as Jan-00 or Jun-03, etc) In a group of cells, 1 each for the years 1999 thru 2004 I'd like to have a total of dollars that were spent for each year, with the values that were summed located in column V as noted above. For example: Col:V Col:Z 55.62 Jan-99 12.50 Feb-04 104.88 Sept-04 0.12 Aug-03 RESULTS A summary cell for each year would show: That in 1999 purchases were 55.62 In 2003, purchases were 0.12. In 2004 purchases were 117.38 Thanks in advance for any ideas to make this happen. Pierre |
#2
|
|||
|
|||
One easy way:
Use a Pivot Table. Put the Date in the left hand field, and sum of Amount in the main field. Right-click the date field, choose Group, and select Year from the listbox. An example: ftp://ftp.mcgimpsey.com/excel/pierre_cowguy_demo.xls In article .com, "Pierre" wrote: I've tried SUMIF, and SUMPRODUCT. . .Trying to total values that occurred within a given year: We have a column of historical costs, and a corresponding item purchase date. The historical costs to total are in a range such as V5:V2050. (Some cells may be blank) The dates those values were purchased are in a range such as Z5:Z2050 (formatted as Jan-00 or Jun-03, etc) In a group of cells, 1 each for the years 1999 thru 2004 I'd like to have a total of dollars that were spent for each year, with the values that were summed located in column V as noted above. For example: Col:V Col:Z 55.62 Jan-99 12.50 Feb-04 104.88 Sept-04 0.12 Aug-03 RESULTS A summary cell for each year would show: That in 1999 purchases were 55.62 In 2003, purchases were 0.12. In 2004 purchases were 117.38 Thanks in advance for any ideas to make this happen. Pierre |
#3
|
|||
|
|||
JE,
Thank you for the reply, and the fix. I tried it with the example provided, as well as some of my regular data. It does work, however, but it doesn't like the fact that there are blank cells within the field ranges, and is unable to group them . "Cannot group that selection" is the result. I take out the blanks, and it's fine. (The sheet, still needs to have the blanks, as these rows contain other data in other columns, so reformatting the sheet isn't an option.) Do you have a "plan B"? Thanks again. Pierre |
#4
|
|||
|
|||
Hi,
One solution is: AA......AB 1999....=SUM(($V$5:$V$2050)*(YEAR($Z$5:$Z$2050)=AA 1)) ............. 2004....=SUM((... Confirm the Formula with Ctrl+Shift+Enter not just Enter. Regards, Ola |
#5
|
|||
|
|||
I gotta tell ya, I like that one too. Works like a champ. However, I
should've said. . . the "blank" cells, really aren't blank, but contains LOOKUP formulas, and IF functions. If it doesn't find a value in its lookup procedure, it returns a blank. The =SUM formula referenced above works well, unless I include one of the cells in which it didn't find a value, and subsequently displayed a blank cell. (If the cell is 'truly' blank, the formula also works fine.) Thoughts? Thanks again for any assistance. Pierre |
#6
|
|||
|
|||
Expensive...
=SUM(IF(Year(DateRange)=YearCrit),IF(ISNUMBER(Cost Range),CostRange))) which you need to confirm with control+shift+enter instead of just with enter. Pierre wrote: I gotta tell ya, I like that one too. Works like a champ. However, I should've said. . . the "blank" cells, really aren't blank, but contains LOOKUP formulas, and IF functions. If it doesn't find a value in its lookup procedure, it returns a blank. The =SUM formula referenced above works well, unless I include one of the cells in which it didn't find a value, and subsequently displayed a blank cell. (If the cell is 'truly' blank, the formula also works fine.) Thoughts? Thanks again for any assistance. Pierre |
#7
|
|||
|
|||
I translated that into :
=SUM(IF(Year($Z$5:$Z$2050)=AS2),IF(ISNUMBER($V$5:$ V$2050),$V$5:$V$2050))) It stops and blinks at the entry AS2, and displays a get help with the formula message. (in AS2, I've got a 4 digit date: 1999) Column Z are the dates Column V are the prices Once more with feeling. Thanks for your interest. Pierre |
#8
|
|||
|
|||
Extraneous paren, probably my fault...
=SUM(IF(Year($Z$5:$Z$2050)=AS2,IF(ISNUMBER($V$5:$V $2050),$V$5:$V$2050))) followed by control+shift+enter. Pierre wrote: I translated that into : =SUM(IF(Year($Z$5:$Z$2050)=AS2),IF(ISNUMBER($V$5:$ V$2050),$V$5:$V$2050))) It stops and blinks at the entry AS2, and displays a get help with the formula message. (in AS2, I've got a 4 digit date: 1999) Column Z are the dates Column V are the prices Once more with feeling. Thanks for your interest. Pierre |
#9
|
|||
|
|||
Sorry,
Try this: AB1: =SUM(IF(ISNUMBER($V$5:$V$2050+$Z$5:$Z$2050);($V$5: $V$2050)*(YEAR($Z$5:$Z$2050)=AA1);0) Ola |
#10
|
|||
|
|||
Ola, this generated a #VALUE
=SUM(IF(ISNUMBER($V$5:$V$2050+$Z$5:$Z$2050);($V$5: $V$2050)*(YEAR($Z$5:$Z$2050)=AS2);0)) (After the semicolon, the 2 ranges are not hilighted in a color, indicating that it doesn't like it.) Pierre |
#11
|
|||
|
|||
Could it be that all ; should be , ?
It works fine for me. Even with Blanks, Text and #Value! in the V and Z column. Ola |
#12
|
|||
|
|||
Ta Daa!!!!
Thank you! Its beautiful :) Pierre |
#13
|
|||
|
|||
Happy to evoke loud sounds*,
and thanks for the feedback. Ola *not noice |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero values in a log chart | Charts and Charting in Excel | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
Newbie to charts question - projecting values between data points | Excel Discussion (Misc queries) | |||
Save External Link Values | Excel Discussion (Misc queries) | |||
linked values in a chart | Charts and Charting in Excel |