# Nested IF and SUMIFS - Multiple criteria based on an IF

1. ## 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.  Register To Reply

2. ## Re: Nested IF and SUMIFS - Multiple criteria based on an IF

Note: you can nest 64 IF's in Excel 2010, the 7 limit was increased in 2007 version The problem is you are saying
=A13 = "9/1/2014"

However, What Excel sees is
=41883 = "9/1/2014"
Which will be false

When you do a mathematical comparison on certain values in excel (such as dates and times), it will convert them into numbers that can be compared mathematically. All dates in excel are represented by serial numbers.
A similar thing can be observed if you do "=(1=2)" in a cell, it will say FALSE. But if you write "=(1=2)+0", putting some math in there, it will give you a 0, the number representing FALSE

Try like this to see:

=IF(TEXT(A13,"m/d/yyyy")="9/1/2014",SUMIFS(B11:B16,A11:A16,">8/1/2014",A11:A16,"<=9/1/2014"),"nope")

(Forces the format of A13 into m/d/yyyy format, then compares)  Register To Reply

3. ## Re: Nested IF and SUMIFS - Multiple criteria based on an IF

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))  Register To Reply

4. ## Re: Nested IF and SUMIFS - Multiple criteria based on an IF

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??  Register To Reply

5. ## Re: Nested IF and SUMIFS - Multiple criteria based on an IF

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.  Register To Reply

6. ## Re: Nested IF and SUMIFS - Multiple criteria based on an IF

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

There are currently 1 users browsing this thread. (0 members and 1 guests) 