+ Reply to Thread
Results 1 to 12 of 12

Sum special range

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Sum special range

    Hello everybody
    In my attachment I have a selection for the range A1:I11 .. the last row is blank ..
    Based on selection I want to get the results in Column J based on Column G
    Situations:
    G1 is blank so J1 is blank.

    G2 has a unique name so J2 will have the same value as in I2.

    G3 and G5 and G6 and G10 has the same name so I want to sum the values in column I which related to that name and the result appear once at J3

    G4 and G7 the same situation as G2.

    G8 and G9 has the same name so to sum the values in Column I related to those names and to get the result in G8 only

    The desired results are shown in the attachment
    Attached Files Attached Files

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,376

    Re: Sum special range

    maybe so (in J1)
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: Sum special range

    Thanks for the formula but it doesn't fulfill the desired results..
    I want to do the task by code and if possible depending on the selection
    I mean when writing the code to be like that

    With Selection


    End With

    As the range will be selected first.. I have other ranges like that

  4. #4
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: Sum special range

    Hi,

    While we are waiting someone make a code with Scripting.Dictionary object , let me propose this array method :

    Please Login or Register  to view this content.
    Regards

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: Sum special range

    Great Mr. Karedog
    Fantastic wonderful
    I have many ranges like that but not the same number of rows of course ..
    I want to apply that to all the ranges ... between each range there is a blank row..

    Or if it difficult somewhat I want the code to be suitable for the selection
    If I select the range A1:I11 including the blank row and run the code it works well
    I hope it is clear

  6. #6
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: Sum special range

    I tried to edit your code like that and it works for that range only.
    Is there a way to do that for all ranges?

    Please Login or Register  to view this content.
    Last edited by YasserKhalil; 01-09-2015 at 02:06 PM.

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: Sum special range

    Between ranges there are blank rows and each group of rows is considered separeted from the other ranges ..
    I mean it looks like blocks between each block there is a blank row

    Here's a nother attachment
    Attached Files Attached Files
    Last edited by YasserKhalil; 01-09-2015 at 03:19 PM.

  8. #8
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,877

    Re: Sum special range

    Quote Originally Posted by YasserKhalil View Post
    I tried to edit your code like that and it works for that range only.
    Is there a way to do that for all ranges?
    Your modified code works well, just remember to select only last 3 columns only before run the macro (as my initial code --> mtx = Range("G1:I10") --> G:I = last 3 columns)




    Quote Originally Posted by YasserKhalil View Post
    Between ranges there are blank rows and each group of rows is considered separeted from the other ranges ..
    I mean it looks like blocks between each block there is a blank row

    Here's a nother attachment
    Here is the code to fulfill your requirement :

    Please Login or Register  to view this content.
    Regards

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: Sum special range

    Why VBA?

    Formula in J2
    =IF(COUNTIF(G$2:G2,G2)=1,SUMIF(G:G,G2,I:I),"")

    VBA
    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: Sum special range

    Mr. jindon
    Did you see the attchment at post number #7?
    The desired results are different from your results
    The problem is that each range is considered as a spearated unit..

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,418

    Re: Sum special range

    Ok then
    Please Login or Register  to view this content.

  12. #12
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,840

    Re: Sum special range

    Thanks a lot Mr. Jindon for this perfect code..

+ 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. Copy range using paste special
    By Shaun1978 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-07-2013, 09:45 AM
  2. [SOLVED] Range in code gives special problem
    By abjac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-17-2013, 03:21 AM
  3. Select Range Paste Special
    By additude in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 12-30-2007, 01:09 PM
  4. select special range
    By harold84 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2007, 07:25 AM
  5. [SOLVED] Range name special characters
    By Charley Kyd in forum Excel General
    Replies: 3
    Last Post: 02-11-2005, 06:06 PM

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