|
|
Post by kryssy on Jul 27, 2015 22:38:59 GMT
I'm a self-taught, youtube-tutorial-watchin' Excel person, and usually I can google and figure out the formula I need by substituting here and there... but this has me stumped.
I need a formula for adding up one column of values ($) when the date in another column is anything in 2015 (in the form of 2/14/15, etc). AND just to complicate things, I need the formula on a different sheet than the columns (the sheet with the columns is called "Jobs," and I know I put "Jobs!" in front of the range...? but I don't know when I need parentheses/commas and when I don't, and trial and error isn't working...)
If it helps, the column of values to add is I, and the column of dates is N.
Any Excel gurus on now, who can whip this out? TIA!
|
|
|
|
Post by utmr on Jul 27, 2015 22:55:16 GMT
You want a sumif function. Can't do it from my phone but it will be sumif(range with dates, range with dollars).
|
|
|
|
Post by kryssy on Jul 27, 2015 23:08:14 GMT
This is the last thing I tried:
=SUMIF((Jobs!I2:I10000,N2:N10001,">=2014-01-01",)(Jobs!N2:N10000,"<2014-12-31"))
Somethin' ain't right, but I can't figger out what it is...
|
|
|
|
Post by misadventurous on Jul 28, 2015 1:36:04 GMT
This should work:
=SUMIF(Jobs!N2:N10000,">="&42005,Jobs!I2:I10000)
The symbols in the criteria need to be enclosed in quotation marks and concatenated (with the &) with the start date (42005 is Jan 1 2015).
|
|
|
|
Post by kryssy on Jul 28, 2015 2:27:56 GMT
This should work: =SUMIF(Jobs!N2:N10000,">="&42005,Jobs!I2:I10000) The symbols in the criteria need to be enclosed in quotation marks and concatenated (with the &) with the start date (42005 is Jan 1 2015). Well, it's a valid formula (I'm not getting an error message), but it's giving me a result of $0.00. And I know I've made some money this year...  Thanks for trying, both of you... I'm determined to figure this out! |
|
|
|
Post by utmr on Jul 28, 2015 13:55:16 GMT
Try changing the date portion to ">12/31/2014" formatted just like it is formatted in col N
|
|
|
|
Post by kryssy on Jul 28, 2015 14:35:53 GMT
OK, here's what I just tried: =SUMIFS(I2:I10001,N2:N10001,”>=2014-01-01”,N2:N10001,”<=2014-12-31” And I tried it on the same sheet as the columns, just to see if it would work there... and it gives me an error.  |
|
|
|
Post by Darcy Collins on Jul 28, 2015 14:52:30 GMT
Dates are in N and numbers to be added in I =SUMIF(Jobs!N2:N10000,">12/31/2014",Jobs!I2:I10000) If this doesn't work your dates aren't formatted correctly. ETA the first part of the sumif is what you're comparing, the second is the criteria, the third is what you're actually summing. ETA more: this is a tutorial to do exactly what you're asking - it uses an example of money earned before a certain date instead of after, but otherwise it's exactly the same: www.exceltrick.com/formulas_macros/excel-sumif-and-sumifs/I don't see why you're now using sumifs as you don't have multiple conditions |
|
|
|
Post by utmr on Jul 28, 2015 14:55:44 GMT
OK, here's what I just tried: =SUMIFS(I2:I10001,N2:N10001,”>=2014-01-01”,N2:N10001,”<=2014-12-31” And I tried it on the same sheet as the columns, just to see if it would work there... and it gives me an error. Is the range on the same sheet as the formula? Or do you need to add the sheet name? If you only want 2015 data summed, try ">2014-12-31" but all the dates have to be formatted exactly the same. Try highlighting your dates and redoing the format, just to be sure. |
|
|
|
Post by utmr on Jul 28, 2015 15:03:32 GMT
Also take the s off sumif.
=SUMIF(N4:N25,">12/31/2014",I4:I25)
Where N is the range with the dates and I is the range with the dollars
ETA Darcy and I were typing at the same time. :-)
|
|
|
|
Post by jennyap on Jul 28, 2015 15:09:55 GMT
It has to be the date format: I just used this (within the same sheet) and it gave me the correct result =SUMIFS(I2:I10001,N2:N10001,">=01/01/2015",N2:N10001,"<=31/12/2015") My dates in col N are formatted 01/01/2015 ie exactly the same as in the formula. If yours are different, change the formula dates to match the ones in col N I wouldn't change to a SUMIF, you're right to use SUMIFS so that once you get into 2016 it still only picks up 2015 data  ETA Am I right that both the amounts and dates (the columns N & I we're referring to) are on the Jobs sheet? If so you need the Jobs! in front of each of the ranges, like this: =SUMIFS(Jobs!I2:I10001,Jobs!N2:N10001,">=01/01/2015",Jobs!N2:N10001,"<=31/12/2015") |
|
|
|
Post by kryssy on Jul 28, 2015 16:15:40 GMT
OK, this one worked: =SUMIF(Jobs!N2:N10000,">12/31/2014",Jobs!I2:I10000) Maybe it's the SUMIF instead of SUMIFS...? And I can change it to "<01/01/2015" to get all the payments in 2014! Thank you, Darcy, and everyone else who chimed in! The RefuPeas are a wealth of knowledge... |
|
