+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 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 Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    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



  3. #3
    Forum Guru 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 Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,318

    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 Guru 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
    MS-Off Ver
    Excel 2007
    Posts
    4,790

    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: 
    GC3:50, GD3:G50, [B]GE3:GE50[/B]
    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: 
    GF3:GF50 GG3:GG50 [B]GH3:GH50[/B]
    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 Guru 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: 
    =SUMPRODUCT(($FU$3:$FU$82="A")*(COUNTIF($B$3:$B$50,$EW$3:$FA$82)>0))
    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
    2007, 365 Insiders
    Posts
    11,302

    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

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

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