+ Reply to Thread
Results 1 to 12 of 12

Sum special range

  1. 01-09-2015, 01:08 PM #1
    YasserKhalil
    YasserKhalil is offline
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    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
    Reply With QuoteRegister To Reply
  2. 01-09-2015, 01:22 PM #2
    nilem
    nilem is offline
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Sum special range

    maybe so (in J1)
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 01-09-2015, 01:35 PM #3
    YasserKhalil
    YasserKhalil is offline
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    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
    Reply With QuoteRegister To Reply
  4. 01-09-2015, 01:50 PM #4
    karedog
    karedog is offline
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    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
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important
    Reply With QuoteRegister To Reply
  5. 01-09-2015, 01:57 PM #5
    YasserKhalil
    YasserKhalil is offline
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    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
    Reply With QuoteRegister To Reply
  6. 01-09-2015, 01:58 PM #6
    YasserKhalil
    YasserKhalil is offline
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    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.
    Reply With QuoteRegister To Reply
  7. 01-09-2015, 03:13 PM #7
    YasserKhalil
    YasserKhalil is offline
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    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.
    Reply With QuoteRegister To Reply
  8. 01-10-2015, 12:03 AM #8
    karedog
    karedog is offline
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    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
    Reply With QuoteRegister To Reply
  9. 01-10-2015, 12:26 AM #9
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,516

    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.
    Reply With QuoteRegister To Reply
  10. 01-10-2015, 01:24 AM #10
    YasserKhalil
    YasserKhalil is offline
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    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..
    Reply With QuoteRegister To Reply
  11. 01-10-2015, 01:30 AM #11
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,516

    Re: Sum special range

    Ok then
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 01-10-2015, 08:11 AM #12
    YasserKhalil
    YasserKhalil is offline
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Sum special range

    Thanks a lot Mr. Jindon for this perfect code..
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1