Sumifs with date range as criteria in Excel
SUMIFS with date range as criteria
I have exported some data from our
accounting system and I'm trying to come up with a formula that will sum column
D only for the rows that fall within a specific date range (ex.
10/1/2011-10/31/2011 or 11/1/2011-11/30/2011). The other criteria that I require
is that the sum is for each unique instance of column A + column B.
I
have tried the following formula, as well as several variations of it, but I
have not had any luck with anything that I've tried. I have even created a
concatenated value for column A+B (ex. SMITH,JOHN07313) to see if that was the
problem, but still no luck.
=SUMIFS(D:D,A:A,A3,B:B,B3,E:E,E3>10/1/2011,E:E,E3<10/31/2011)
I would
like to have this formula in several different columns for each project (column
B) that we have... each having a different budget period.
If I am not
explaining my problem with enough detail please let me know and I will try to
expand on it.
Thank you in advance!
*This is just a portion of the
data (with names changed for privacy reasons)
Column A Column B Column C Column D Column E SMITH,JOHN 07313 1-5221 ($11.11) 10/02/2011 SMITH,JOHN 07313 1-5221 $18.51 10/14/2011 SMITH,JOHN 07313 1-5220 ($28.66) 11/15/2011 DOE,JANE 07500 1-5220 $47.78 10/07/2011 DOE,JANE 07500 1-5210 ($8.27) 10/22/2011 DOE,JANE 07500 1-5210 $13.78 11/11/2011 DOE,JANE 07500 1-5112 ($111.06) 11/15/2011 DOE,JANE 07500 1-5112 $185.10 12/15/2011
Answer:
the correct formula should be =SUMIFS(D:D,A:A,A3,B:B,B3,E:E,">10/1/2011",E:E,"<10/31/2011")
(for formulas, depending on your country, you might have to change ; with , or the opposite
