+ Reply to Thread
Results 1 to 2 of 2

Multiple Condition Sum Between Dates that is dependent on a Data Validation List.

  1. #1
    Registered User
    Join Date
    06-11-2018
    Location
    USA
    MS-Off Ver
    2010 (Work) 2016 (Home)
    Posts
    1

    Cool Multiple Condition Sum Between Dates that is dependent on a Data Validation List.

    This may be a tough one, good luck. - Thanks in advance!

    IFS(SUMIFS($G3:$G40, $B3:$B40, "READING", $C3:$C40, "COMPLETE"), IFS(M2=2018, SUMIFS($G3:$G40,$E3:$E40,">="&"1/1/2018",$E3:$E40,"<="&"12/31/2018"), M2=2019, SUMIFS($G3:$G40,$E3:$E40,">="&"1/1/2019",$E3:$E40,"<="&"12/31/2019"), M2=2020, SUMIFS($G3:$G40,$E3:$E40,">="&"1/1/2020",$E3:$E40,"<="&"12/31/2020")))

    ^what I have now^

    I want a formula that depends on a Data Validation list of 2018, 2019, 2020.
    When one of those values is selected in the drop down the formula is supposed to validate 3 conditions.
    1st condition: category column has "reading" selected in the row
    2nd condition: status column has "complete" selected in the row
    3rd condition: date column falls within the year selected in the row


    i.e. (when 2018 is selected in cell $M2 and if the category column is "reading' in row 3 and status column is "complete" in row 3 then it should check if the date column in row 3 is between 1/1/2018 and 12/31/2018 ... and if all is true then it would go to the pages column and sum the value in row 3) [respectively if row 4 had those conditions fulfilled the formula would sum row 3 pages' column value and row 4 pages' column value]

    haaalp.PNG

    haaalp_2.PNG
    Last edited by JarSanYou; 06-11-2018 at 10:39 AM. Reason: Has been resolved.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Multiple Condition Sum Between Dates that is dependent on a Data Validation List.

    Try

    =SUMIFS($G$3:$G$40,$B$3:$B$40,"READING",$C$3:$C$40,"COMPLETE",$E$3:$E$40,">="&DATE(M2,1,1),$E$3:$E$40,"<="&DATE(M2,12,31))

+ 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. Dependent Data Validation Drop Down List for multiple rows?
    By Howie Dean in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-16-2018, 05:31 PM
  2. Replies: 1
    Last Post: 01-14-2018, 11:04 AM
  3. Data Validation List - Dependent on Adjacent Cell - Value from Unsorted List
    By justforthis1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-26-2013, 12:24 PM
  4. [SOLVED] Dependent data validation on condition
    By PWinkz in forum Excel General
    Replies: 2
    Last Post: 03-26-2013, 08:48 AM
  5. Replies: 2
    Last Post: 07-17-2012, 01:18 PM
  6. Removing multiple invalid characters for dependent data validation list
    By cuclay in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-15-2009, 01:34 AM
  7. [SOLVED] data validation--multiple dependent list
    By Michael in forum Excel General
    Replies: 9
    Last Post: 05-01-2006, 08:15 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