+ Reply to Thread
Results 1 to 17 of 17

Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

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

    Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    Hi,

    Im using SUMPRODUCT formula successfully counting the product Matched on exact row-> (Bold B3, B4, B5..B50)

    this is the formula:
    Please Login or Register  to view this content.
    But now i would like to change the exact row for a range B3:B10, and draw the formula till B50
    (I'm using excel Helper)

    Whats the right way to apply the same formula but using a Range at the end (not just single row)?

    I already tried this : (after the equal i Added the range B3:B10)
    Please Login or Register  to view this content.
    i applied in different way and not displaying what i need.


    Thanks

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    This might be possible, depending (I think) on what's in B3 to B50. Can you post your sheet, or a cut down version of it??
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    It sounds like you want to know if any entry in the range FC3:FG82 matches any entry in the range B3:B10. Is that correct?

    However, I don't understand what this means:

    and draw the formula till B50
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

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

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    Hi Glen and Tony,

    Tony, that exactly what i need to do :
    It sounds like you want to know if any entry in the range FC3:FG82 matches any entry in the range B3:B10. Is that correct?
    I'm sorry i miss spell, i mean
    Drag the formula tIll $B$50
    thanks
    Last edited by Franky alta; 11-07-2015 at 10:55 AM. Reason: change html to wrap code instead

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    As requested earlier....

    Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    The paperclip icon

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

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    Hi Glenn,

    Yes i'm preparing the attach..

    Thanks

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    I'm still not sure what this mean:

    Drag the formula tIll $B$50
    Does that mean the range B3:B10 should actually be B3:B50?

    See if you can adapt this:

    =SUMPRODUCT(($FU$3:$FU$82="A")*ISNUMBER(MATCH($FC$3:$FG$82,$B$3:$B$10,0)))

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    Try this also
    Please Login or Register  to view this content.

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

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    Hi,

    please find attach the SUMPRODUCT sample sheet

    I'm going to try right now the new formulas


    Thanks
    Attached Files Attached Files
    Last edited by Franky alta; 11-07-2015 at 05:16 PM. Reason: Replaced the sample sheet, better explained

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

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    Mr Valko,

    i'll need to get only the top 8 values (B3:B10) matched on a range B3:B50 after new formula using autofilter and i'll sort smallest to largest on columns
    HTML Code: 
    the sort will be based on values column GE3:GE50 (where sumproduct formula goes) where will be displayed the result.
    i'm going to apply the same autofilter on column
    HTML Code: 
    based on values column GH3:GH50 and so on..

    thanks
    Last edited by Franky alta; 11-07-2015 at 12:06 PM.

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    Ok, now I'm totally confused!

    I'll let someone else tackle this.

    Good luck!

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

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    Hi Mr Tony Valko and Mr kvsrinivasamurthy

    I adapted both formula and as result it display value 1(Mr Valko) and 7 (Mr kvsrinivasamurthy) on each row of Column GE (GE3 TO GE50)
    HTML Code: 
    The Result that i'm expecting in column GE is :
    GC___GD___GE___
    29___31___1
    05___15___2
    06___15___2
    24___13___1
    07___12___1
    28___12___1
    27___10___1
    02___8____0
    22___8____0
    14___7____0
    23___7____0

    and the reason is that Column FU3 and FU7 is the String "A" and next to it at left
    EW3:FA3 Have values -> 12__15__10
    and
    EW7:FA7 Have values -> 13__15__31

    and these values (EW3:FA3) and (EW7:FA7)matched X TIMES with Top values in Column GE, so values with zeroes not matched at all,

    please see new sample attach with full explanation.

    Hope this explanation is clear what i need..

    earlier I tried to explain and do the thing fast, but it i confused you, I'm sorry


    Thank you,
    Attached Files Attached Files

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

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    HI,

    BUMP Please

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

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    Good morning..


    Bump

  15. #15
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    The Result that i'm expecting in column GE is :
    GC___GD___GE___
    29___31___1
    05___15___2
    06___15___2
    24___13___1
    07___12___1
    28___12___1
    27___10___1
    02___8____0
    22___8____0
    14___7____0
    23___7____0
    That's the result I'm getting, but that is not what the initial upload indicates for the 12s in column GD. Here's my formula. Array-enter this in GF3 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Dave

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

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    Hi Mr dave,

    I'm sorry to bother because of my dummy question,
    The question or dilemma : The formula work but i see have a (GD3= (i tough a formula might use a range i.e GD3:GD10)
    I don't think but Is possible to use a range instead of single column?, I didn't mention (because Col B is the same Col GD using Indirect)
    because errors in excel can i use B3:B10 to do the same job??
    How do I use top GD3:GD10 (or B3:B10) or vice versa in the formula ?
    is possible?
    what i need is to pick the 8 top score of either GD or B Col (is the same) count these score in Range EW:FA if belong to "A" (FU Col) then
    display GE3 :GE50 whatever matched and counted column of GD3:GD50 but everything based on top 8 or 10 integer GD3:GD10 (or B3:B10)

    Hope no confuse you..

    thanks !!

  17. #17
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Applying SUMPRODUCT on few ranges for exact row, need to change for a Range

    Well, yes I'm confused. In fact I no longer know what the questions are. Let me set with this awhile.

+ 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. Code for applying same change to range of existing formulas
    By MontrealMTL in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-13-2015, 03:38 PM
  2. [SOLVED] Worksheet change event applying to multiple ranges
    By MaddyG in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-29-2013, 12:04 PM
  3. Name range that will change size multiple ranges
    By powdow in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2013, 03:18 PM
  4. How to change X axis range using dynamic ranges
    By james444 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-12-2013, 04:23 PM
  5. if then else of ranges of a col that change the value of range of diff col values
    By ferrum_equitis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2012, 06:45 AM
  6. sumproduct sum range w/ multiple ranges
    By jw01 in forum Excel General
    Replies: 4
    Last Post: 10-25-2012, 08:51 PM
  7. Applying Formulae to Ranges
    By RexG in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-31-2011, 09:20 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