|Look at my formula:|
=SUMIFS(D2:D6, B2:B6, ">="&DATEVALUE("5/1/2011"),
=SUMIFS(Sheet1!G2:G112,Sheet1!B2:B112,">&DATEVALUE(5/1/2011), Sheet1!B2:B112, "<DATEVALUE("6/1/2011"),Sheet1!E2:E112,"Groceries")
Note specifically the differences in these portions of your formula:
Mine: ">="&DATEVALUE("5/1/2011") and "<"&DATEVALUE("6/1/2011")
Yours: ">&DATEVALUE(5/1/2011) and "<DATEVALUE("6/1/2011")
1 - You have the quotation marks in the wrong places.
2 - You didn't use the Concatenation operator (&) like I did.
3 - Even if you had items 1 and 2 correct, you are only checking for dates greater than "5/1/2011" not greater than or equal to "5/1/2011" so the formula will ignore "5/1/2011".
You need to separate out the operators (>, etc.) as text by using the quotes and then concatenate that with the DATEVALUE function. If you put the DATEVALUE function inside the quotes, Excel will think that it is just text and not evaluate the function.
You should review both functions (SUMIFS and DATEVALUE) in the Excel help files so you know how each one works and the correct syntax for each. Once you understand how each one works on its own, you'll have a better understanding how to combine them for your use.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.