+ Reply to Thread
Results 1 to 15 of 15

How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

  1. #1
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    Hi everyone,


    Now i have another question regarding the INDIRECT formula applied to SUMPRODUCT.
    this time to sumproduct of 2 cell when have match (not the match formula) in 2 ranges,
    Note:
    my main purpose is to keep at all the time the same cell references with formulas that is applied to dynamic range.
    (i have to apply INDIRECT in a hundreds of cell that use different formulas)


    the actual formula that i'm using work good and this is the task:
    Let say that cell E4 is equal 0 and cell M4 equal 6
    then the formula look if 0 and 6 are in the same day and sum the times founded in ranges E37 to I137 and range Q37 TO U137

    as i said my formula is working good but i have no clue where is supposed to be inserted the INDIRECT function....
    I googled in many ways and i think google got tire of me

    This is the good formula that need the INDIRECT:
    Please Login or Register  to view this content.
    I have tried many of these formulas with not success
    Please Login or Register  to view this content.

    any help or suggestion is welcome

    Thank you so much
    Last edited by Franky alta; 06-28-2015 at 08:36 PM. Reason: add few words

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    Hi,

    It's not clear to me why you need an INDIRECT function.

    Please upload the workbook and manually add some results and explain in a note how you calculated them.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    Hi Franky,

    I'm with Richard above. It is too hard to tell what you are really trying to do and why you need "Indirect". See:
    http://www.cpearson.com/excel/indirect.htm
    for a good explanation of what Indirect does.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    Re: How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    Hi Richard buttrey,

    The main purpose is the keep the some formulas in same cell references at all the time due that i the formulas are in top 15 row/columns
    and the data start to analized in row/cell A:37 till VU:37 (read data from top/down),
    So, when a new value is inserted in a new row 37(yes, row insertion), all the data moves 1 row down, then the cell from top formulas change the cell references from let say from C37 to C38 and so on.

    on the top formulas i need to keep at all the time the same cells references after each time new values are updated on the new inserted row.

    the file is big and i'm editing in order to send a good sample


    Thank you
    Last edited by Franky alta; 06-28-2015 at 09:10 PM.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    Hi Franky,

    This sounds like a job for Dynamic Named Ranges. They adjust with how many rows (or columns) of data you have. Then you use the DNR in the formula instead of the other notation. Read about them at:
    http://www.bettersolutions.com/excel...G820716330.htm
    http://www.ozgrid.com/Excel/DynamicRanges.htm

    Note the first sentence of the above line of:

    Possibly one of Excels most underutilized aspects is its ability to create dynamic named ranges that will expand and contract according to the data in them.

  6. #6
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    Re: How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    Hi Mr MarvinP,

    Yes sir i'm aware that i need to study some more, but i'm in that process,
    Actually i'm studying online in "UDE...." (i can't mention a business name in any post)
    and i really try to find info, but didn't find info related to 2 ranges and 2 match (4 ranges in total) using such formula with the indirect.


    Thank you Marvin

  7. #7
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    Re: How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    Hi Mr marvinP,

    the thing is that i already have a formula that work good, and i just need to know where can be inserted the INDIRECT function

    Please Login or Register  to view this content.

    right now i'm going to check the links


    thank you

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    Franky, I suggest you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like. (2 other members have already asked this )
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    Re: How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    Hi Mr Fdibbins,


    Yes two members asked me about sample sheet. But the file is Big and when edit many of the values ranges change to like error
    e.i ###,###,###,###

    that's why i haven't send... sorry i'm trying to fix it.


    Best Regards

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    If necessary, create a dummy workbook that resembles your actual data. We just need to see a SMALL amount data that is directly related to your question

  11. #11
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    Re: How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    Hi mr FDibbins,

    Please find Sample file attached, I made smaller the file but for some reason the sheet can't show the values, instead it show ####,
    but the actual formulas exist in each cell.


    Note:
    my main purpose is to keep at all the time the same cell references with formulas that is applied to dynamic range.
    Attached Files Attached Files
    Last edited by Franky alta; 06-29-2015 at 02:11 AM.

  12. #12
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    Re: How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    Hi Mr MarvinP,

    I study the first link and second link suggested and it have nothing to do to keep at all time the cell references.
    these two link mostly is referenced to OFFSET function
    and still i don't get it how come I can apply such formula in my worksheets update, i'm sorry about my naive aptitude or incomprehension..

    Note
    my main purpose is to keep at all the time the same cell references with formulas that is applied to dynamic range.
    Last edited by Franky alta; 06-29-2015 at 02:09 AM.

  13. #13
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    Franky,

    I'm sorry but this still isn't clear to me at all. You have going on 600 columns of data - one of the largest set of column data I've ever seen, but you have not, as Ford suggested shown us what results you expect to see. i.e. Manually add some results showing the position and result AFTER you have performed whatever action you want. And tell us what it is that you are DOING

    But better still tell us in a narrative form what your data represents and what you are trying to do with it. i.e. explain what the formulae columns are telling you. Every one of the ~600 columns apart from D & KI contains formulae and I can't help thinking there might be a simpler way of getting the information you want without all these columns.

    For instance do you want to see all the 600 columns all the time or just a very small subset if you were to enter some variables in a few cells which would then generate a specific set of results.

  14. #14
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    Re: How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    Hi Mr Richard Buttrey.

    I think i found a solution.
    My Main goal is to to keep at all the time the same cell references with formulas that is applied to dynamic range.
    This is my current working formula that need the indirect.
    Please Login or Register  to view this content.
    i'm going to leave these formulas with the cell references (luckily there are only 396)
    and i'm going to create a "click" macro, which means i'm going to click each of these cell (while the macro is recording).
    then after each time when add the new row 37 and add the new data in such row, i'll run the macro and i'm going to have the cell reference as i need it.

    Thank you


    Regards

    Franky

  15. #15
    Registered User
    Join Date
    06-21-2015
    Location
    fort lauderdale, florida
    MS-Off Ver
    2007
    Posts
    77

    Re: How to apply correctly INDIRECT in SUMPRODUCT for 2 RANGES when 2 cell match

    Hi Mr RiButtrey,

    at your question of the 600 columns, most of the formula work with each report, and each report are dependable from 1 data. (E37 TO I37 down)


    Thank you sir.


    Regards

    Franky
    Last edited by Franky alta; 06-29-2015 at 10:06 AM. Reason: add report word

+ 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] How to apply correctly INDIRECT in array with long formulas
    By Franky alta in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-28-2015, 05:13 PM
  2. [SOLVED] How to apply correctly SUM INDIRECT to non continuous column?
    By Franky alta in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-28-2015, 02:17 PM
  3. INDIRECT function not working inside MATCH with dynamic ranges
    By fotografer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-26-2014, 02:09 AM
  4. Replies: 0
    Last Post: 09-21-2013, 09:03 PM
  5. Indirect with Named ranges - Control cell
    By danxt in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-23-2007, 11:40 AM

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