Using dates as criteria in sumif formula

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 10:00 PM GMT

Question

Working in07, I have a worksheet with months of the year in a row (H1:S1), and monthly budgets in corresponding row beneath (H3:S3). I'm trying to add a column with "year to date" figures that will sum all the values up to a date specified elsewhere on the sheet. Using the formula =SUMIF(H1:S1,"<07/31/10",H3:S3) to get all figures to end of July works fine but when I try to replace "<07/31/10" with a cell reference "<F$2$", where cell F2 contains the exact same date, the result drops to zero, which is incorrect. What am I doing wrong?! Pete

Answer

"<F$2$" first of all is incorrect, you may want "<$F$2". However, that's also incorrect because you're comparing the dates to be less than the character "$" and then "F", etc. This is what you want: =SUMIF(H1:S1,"<"&F2,H3:S3) or =SUMIF(H1:S1,"<"&$F$2,H3:S3)

Advertisement

©2021 eLuminary LLC. All rights reserved.