Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2008

Tip: Looking for answers? Try searching our database.

sum cluster of cells based on values

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
caroline - 13 Mar 2008 17:41 GMT
Hello,
I have percentages in column A, and values in column B.
The numbers are organised by groups separated by blank cells. I want to be
able to sum clusters in B based on values in A with the following exception
Sum all values Except when there are multiple occurences of 80%, stop at the
first occurence of 80%

For instance,
From A2 to A5: 30%,60%,80%,80%
From B2 to B5: 12,13,24,56
will give (12+13+24) in B1

A8:100%
B8:45
will give (45) in B7

From A12 to A14:80%,80%,80%
From B12 to B14:13,78,23
will give (13) in B11
Etc

Any idea very welcome
Thank you

Signature

caroline

Peter T - 13 Mar 2008 19:03 GMT
If I follow, I take it each value is something like this

Sum(block)  - (count of 0.8's in bloc - 1) * 0.8

Assuming the above is right, you don't say if you want formulas in
appropriate cells to do the calculation or for VBA to work out each value.
Following attempts to do it both ways

Sub Summer()
Dim dbl As Double
Dim i As Long, n As Long
Dim rng As Range, ra As Range, cell As Range
Dim sFla As String
Const CFLA As String = _
 "=SUM(R[-#]C[-1]:RC[-1])-(SUMPRODUCT(--(R[-#]C[-1]:RC[-1]=0.8))-1)*0.8"

On Error Resume Next
With Columns(1)
   Set rng = Union(.SpecialCells(xlCellTypeConstants, 1), _
                   .SpecialCells(xlCellTypeFormulas, 1))
End With
On Error GoTo 0

If rng Is Nothing Then
   Exit Sub ' no values in col-A
End If

For Each ra In rng.Areas
   n = ra.Rows.Count
   sFla = Replace(CFLA, "#", n - 1)
   ra(1).Offset(n - 1, 1).FormulaR1C1 = sFla

   dbl = 0
   i = 0
   For Each cell In ra
       dbl = dbl + cell.Value
       If cell.Value = 0.8 Then i = i + 1
   Next

   ra(1).Offset(n - 1, 2).Value = dbl - (i - 1) * 0.8
Next

End Sub

Regards,
Peter T

> Hello,
> I have percentages in column A, and values in column B.
[quoted text clipped - 19 lines]
> Any idea very welcome
> Thank you
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.