Need totals of values that fall within a given year

  • Thread starter Thread starter Pierre
  • Start date Start date
P

Pierre

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
 
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
 
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
 
Hi,

One solution is:
AA......AB
1999....=SUM(($V$5:$V$2050)*(YEAR($Z$5:$Z$2050)=AA1))
.............
2004....=SUM((...

Confirm the Formula with Ctrl+Shift+Enter not just Enter.

Regards,
Ola
 
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
 
Expensive...

=SUM(IF(Year(DateRange)=YearCrit),IF(ISNUMBER(CostRange),CostRange)))

which you need to confirm with control+shift+enter instead of just with
enter.
 
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
 
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.
 
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
 
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
 
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
 
Back
Top