ExcelBanter - sum cluster of cells based on values

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

"caroline" wrote in message
...
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

--
caroline