How To Add Numbers That Pertain To A Certain Year

[CWEngineer]

I have a spreadsheet with dates in a column such as 1/13/2004, 5/17/2004, 6/8/2005, 9/21/2005. There is a number in the same row for each of those dates such as 3,4,7, and 2 respectively.

I am trying to do a summary that adds all the numbers with year 2004 and 2005.

Such that it gives me 2004 7 in the first row AND 2005 9 in the second row.

Do you guys know how I can do this?

THANKS

 

[IRstuff]

Use an IF statement based on the date serial number range to pull the relevant numbers into a new column and sum that column

TTFN

 

[CWEngineer]

Thanks,

I have a big list of information, and I was trying to find out what is the best way and if you can do a formulat that would just sum up all the values that ended in 2004 and also one that ended in for example 2005. Is it possible to do that?

THANKS

 

[PsionSaint]

You'll need to do what IRStuff suggested first - add a new column with the formula YEAR(A1) in it. This will return the value of the year in the A1 cell. Copy that down the whole list.

Then write a SUMIF statement that uses this new column of years as the range while summing over the numbers column. For example, if you're looking for 2004's total with column B being the new year column you created and column c being the number values to sum:

=SUMIF(B1:B10, 2004, C1:C10)

Make more sumif's for the other year values over the same ranges and you should be done.

 

[KatiLynSki]

If the dates are in C$8:C$17 abd the numbers you want summed are in D$8$17...

B20=1/1/2003
B21=1/1/2004

The following will be the sum of all products in 2003 (print in cell C20 and you can copy the formula and the years down to equal each of the years, you can format column B to show just the year)...
=SUMPRODUCT(--(C$8:C$17<B21),--(C$8:C$17>=B20),D$8$17)

Hope that helps!

-KD

 

[MortenA]

To me this sounds as a pivot table job!

Best regards

Morten

 

[Denial]

If you want a single formula, try this "array formula" (using KatiLynSki's assumed locations)
=SUM(IF(YEAR($C$8:$C$17)=2004,$D$8:$D$17,0))
for the 2004 values. Repeat, mutatis mutandis, for 2005.

[To enter an "array formula", hold down ctrl and shift keys when you hit the enter key.]

 

[joerd]

Using SUMIF as PsionSaint suggested has a quicker calculation than an array formula. In general, array formula's tend to be relatively slow, which you'll notice when you have lots of entries.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Denial]

Joerd is correct regarding calculation speed. However Gman1's second message suggested he had a preference for a single formula rather than a solution that required creating extra columnns.