+ Reply to Thread
Results 1 to 5 of 5

ignore summing values for each item contain shape(oval) for cells

  1. #1
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    ignore summing values for each item contain shape(oval) for cells

    hi
    I would sum the all values from C: N for each item doesn't contain shape(oval) . the result should be in column O with considering will increase data by adding new rows. as in the result in column O when summing values will exclude any cells contains shape (OVAL) .
    thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi, try this !


    According to your attachment a VBA demonstration for starters :

    PHP Code: 
    Sub Demo1()
              
    Dim A$(), L&, T(), VWC%
        
    With ActiveSheet.Shapes
            ReDim A
    (1 To .Count)
            For 
    1 To .Count
                With Range
    (.Item(L).TopLeftCell, .Item(L).BottomRightCell).Cells
                    A
    (L) = .Item(+ (.Count 3)).Address
                End With
            Next
        End With
            ReDim T
    (6 To Cells(Rows.Count1).End(xlUp).Row0)
        For 
    6 To UBound(T)
            
    Evaluate(Replace("IF({1},ADDRESS(ROW(C#:N#),COLUMN(C#:N#)))""#"L))
            
    Application.Match(VA0)
        For 
    1 To 12
            
    If IsNumeric(W(C)) Then V(C) = False
        Next
            T
    (L0) = Application.Sum(Range(Join(Filter(VFalseFalse), ",")))
        
    Next
            Range
    ("O6:O" UBound(T)) = T
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb A variation …


    PHP Code: 
    Sub Demo1v()
              
    Dim A$(), L&, T(), Rc As Range
        With ActiveSheet
    .Shapes
            ReDim A
    (1 To .Count)
            For 
    1 To .Count
                With Range
    (.Item(L).TopLeftCell, .Item(L).BottomRightCell).Cells
                    A
    (L) = .Item(+ (.Count 3)).Address
                End With
            Next
        End With
            ReDim T
    (6 To Cells(Rows.Count1).End(xlUp).Row0)
        For 
    Each Rc In Range("C6:N" UBound(T))
            If 
    IsError(Application.Match(Rc.AddressA0)) Then T(Rc.Row0) = T(Rc.Row0) + Rc
        Next
            Range
    ("O6:O" UBound(T)) = T
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 06-11-2022 at 10:28 AM. Reason: optimization …

  4. #4
    Forum Contributor
    Join Date
    07-14-2021
    Location
    LY
    MS-Off Ver
    2019
    Posts
    271

    Re: ignore summing values for each item contain shape(oval) for cells

    perfect ! much appreciated , your codes works perfectly

  5. #5
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: ignore summing values for each item contain shape(oval) for cells

    Deleted. Not as sophisticated.
    Last edited by jolivanes; 06-11-2022 at 07:33 PM.
    Experience trumps academics every day of the week and twice on Sunday.

+ 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. add oval ring shape in chart excel
    By laksir in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 05-15-2022, 04:41 AM
  2. count oval shape for each name and merge for repeated name
    By Mussa-A in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-14-2021, 09:13 AM
  3. [SOLVED] How to add 3d bevel (Cool Slant) shape effect to Oval shape
    By Steve@Rugby in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-11-2013, 11:29 AM
  4. Graph in a oval shape
    By coreytroy in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 03-22-2013, 02:57 AM
  5. Replies: 4
    Last Post: 08-22-2012, 12:18 PM
  6. Excel 2007 : Controlling shape attributes from cells values
    By koval.andrey in forum Excel General
    Replies: 4
    Last Post: 09-21-2009, 10:01 AM
  7. How to make excel ignore errors when summing up cells
    By abrunstad in forum Excel General
    Replies: 4
    Last Post: 06-02-2009, 11:29 AM

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