+ Reply to Thread
Results 1 to 6 of 6

Nested IF and SUMIFS - Multiple criteria based on an IF

  1. #1
    Registered User
    Join Date
    07-17-2012
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    2

    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. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,213

    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)
    Last edited by Speshul; 10-15-2014 at 02:30 PM.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Valued Forum Contributor
    Join Date
    04-09-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    391

    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. #4
    Registered User
    Join Date
    07-17-2012
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010
    Posts
    2

    Talking 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. #5
    Valued Forum Contributor
    Join Date
    04-09-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    391

    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. #6
    Registered User
    Join Date
    06-12-2009
    Location
    Chester, NY
    MS-Off Ver
    Excel 2013
    Posts
    89

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Nested Sumif's or how to sum data based on nested criteria
    By dlietz in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-07-2014, 11:36 AM
  2. [SOLVED] Nested multiple sumifs
    By Steverizer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-16-2013, 06:58 PM
  3. Replies: 3
    Last Post: 11-21-2012, 04:57 PM
  4. [SOLVED] Nested IF statement to return a value based on multiple criteria
    By harrydnyc in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-27-2011, 11:45 AM
  5. Replies: 1
    Last Post: 05-16-2011, 05:00 PM

Tags for this Thread

Bookmarks

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