×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How To Add Numbers That Pertain To A Certain Year

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

RE: How To Add Numbers That Pertain To A Certain Year

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



RE: How To Add Numbers That Pertain To A Certain Year

(OP)
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

RE: How To Add Numbers That Pertain To A Certain Year

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.

RE: How To Add Numbers That Pertain To A Certain Year

If the dates are in C$8:C$17 abd the numbers you want summed are in D$8:D$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:D$17)

Hope that helps!

-KD

RE: How To Add Numbers That Pertain To A Certain Year

To me this sounds as a pivot table job!

Best regards

Morten

RE: How To Add Numbers That Pertain To A Certain Year

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.]

RE: How To Add Numbers That Pertain To A Certain Year

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.

RE: How To Add Numbers That Pertain To A Certain Year

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources