How To Add Numbers That Pertain To A Certain Year
How To Add Numbers That Pertain To A Certain Year
(OP)
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
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





RE: How To Add Numbers That Pertain To A Certain Year
TTFN
RE: How To Add Numbers That Pertain To A Certain Year
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
RE: How To Add Numbers That Pertain To A Certain Year
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.
RE: How To Add Numbers That Pertain To A Certain Year
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:D$17)
Hope that helps!
-KD
RE: How To Add Numbers That Pertain To A Certain Year
Best regards
Morten
RE: How To Add Numbers That Pertain To A Certain Year
=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.]
RE: How To Add Numbers That Pertain To A Certain Year
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: How To Add Numbers That Pertain To A Certain Year