+ Reply to Thread
Results 1 to 6 of 6

loop issues

  1. #1
    Registered User
    Join Date
    03-27-2005
    Posts
    59

    loop issues

    hi guys

    please can you help me with code for this
    \1

    basically k3 is a data validation list of b3:f3, i want to type 2 dates in i3 and j3 and then after choosing from the list in k3 it add up all the values in that column between those dates and place the answer in g1. hope somebody understands this and can help. thanks for looking and thanks in advance for responces
    Last edited by short_n_curly; 11-20-2010 at 09:55 AM.

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: loop issues

    Hi Short_n_curly,

    A worksheet would be far more valuable when trying to get results. If you can't provide one for confidentiality reasons, at least explain what we're seeing in the image you provided, and let us know what the answer should be in G1 and why.

  3. #3
    Registered User
    Join Date
    03-27-2005
    Posts
    59

    Re: loop issues

    sure there no issues with confidentiality here is the workbook
    the answer in g1 should be a sum from the target column chosen from the list between the dates entered, almost like an sql querey result
    Attached Files Attached Files
    Last edited by short_n_curly; 11-19-2010 at 12:58 PM.

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: loop issues

    To make the formula manageable, I'd suggest a helper cell.

    In K2, enter the following formula:

    =LOOKUP($K$3,{"Alarms","HFRA","In","Out","Replace"},{"D","C","F","B","E"})

    Then in G1 (the merged cell), enter this formula:
    =SUMIFS(INDIRECT($K$2&"4:"&$K$2&"100"),$A$4:$A$100,">="&$I$3,$A$4:$A$100,"<="&$J$3)

    The first formula looks up the value in K3 (the lookup list must be in alphabetical/ascending order, which is why it's not in the same order as it appears on the worksheet) and returns the corresponding column letter.

    The second formula incorporates that column letter using the INDIRECT function. In this example formula, I assumed the ranges to be A4:A100, B4:B100, C4:C100, etc. If your data goes beyond row 100, adjust each instance of 100 to a larger row. (I recommend against using full columns if you don't need to. If you'll never use more than 1000 rows, set it to 1000.)

    Also note that SUMIFS is only available in Excel 2007+. For earlier versions of Excel you would need to use SUMPRODUCT or an array using SUM(IF(.

  5. #5
    Registered User
    Join Date
    03-27-2005
    Posts
    59

    Re: loop issues

    am using 2003

    so changed it to =SUMPRODUCT(INDIRECT($K$2&"4:"&$K$2&"100")*($A$4:$A$100>=$I$3)*($A$4:$A$100<=$J$3)), many many thanks for your help
    Last edited by short_n_curly; 11-19-2010 at 02:40 PM.

  6. #6
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: loop issues

    Glad you got it figured out before I got back.. well done.

    If you wouldn't mind marking the thread as solved that would be wonderful, too.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a drop-down with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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