Need totals of values that fall within a given year

[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

 

[JE McGimpsey]

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

 

[Pierre]

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

 

[Guest]

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

 

[Pierre]

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

 

[Aladin Akyurek]

Expensive...

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

which you need to confirm with control+shift+enter instead of just with
enter.

 

[Pierre]

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

 

[Aladin Akyurek]

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.

 

[Guest]

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

 

[Pierre]

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

 

[Guest]

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

 

[Pierre]

Ta Daa!!!!

Thank you! Its beautiful

Pierre

 

[Guest]

Happy to evoke loud sounds*,
and thanks for the feedback.
Ola
*not noice