Nested IF and SUMIFS - Multiple criteria based on an IF

I have read many of the threads here and can't find the solution. It

I am using Excel 2007 and I am trying to get a solution to a SUMIFS only if a cell meets criteria. I have the SUMIFS working when used by itself, but can't for the life of me figure out how to add it to an nested IF statement.

I am trying to do a summary of the amounts that fall into the previous month. So if 9-1-2014 is populated in A, then SUM the amounts in column B that have dates in column A that fall within the range of 8-2-2014 through 9-1-2014.

The working SUMIFS is:
SUMIFS(B2:B7,A2:A7,">8/1/2014",A2:A7,"<=9/1/2014")
Sum of B2:B7 if A2:A7 falls within the date range.

This list is updated on the date of the charge and does not have the dates pre-populated, so I need the formula to determine if the entry in on the first of the month, if so SUM for the range, if not 0.

IF A2="9-1-2014" then SUMIFS(B2:B7,A2:A7,">8/1/2014",A2:A7,"<=9/1/2014"), IF A2="10/1/2014" then SUMIFS(B2:B7,A2:A7,">9/1/2014",A2:A7,"<=10/1/2014"), IF A2="11/1/2014" then SUMIFS(B2:B7,A2:A7,">10/1/2014",A2:A7,"<=11/1/2014").. and so on for the entire year. (Which is another issue with the nesting limitation of 7)

The date list is not standard and I need to have this calculation only show if the date in A falls on the first of the month.

Please HELP - I have attached my example. IF and SUMIFS issue example.xlsx

Hi,

Not fully understood but try below formula in C2 and copy down:

=IF(DAY(A2)<>1,"",SUMIFS($B$2:$B$7,$A$2:$A$7,">"&DATE(YEAR(A2),MONTH(A2)-1,1),$A$2:$A$7,"<="&A2))

Speshul -

Awesome! Great catch on the date vs text. Your corrections worked. Thank you for the support. (How do I "like" your post?)

misrasomendra -

Absolutely brilliant! not only did it work, it solved the nesting issue by using the date variable.

THANK YOU, THANK YOU!!!!!!!!

You both rock!! How do I "like" or show that your answers worked for the solution??

Glad you solve it and thanks for the feedback, You can mark the thread as SOLVED and click the star icon of ADD REPUTATION below the person comment if you feel the solution solved your query.

I think I'm having a similar issue. Can anyone help? http://www.excelforum.com/excel-form...-new-post.html