# 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.

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

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)

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))

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??

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.

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

##### Users Browsing this Thread

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1