Calculate total for each group

[YasserKhalil]

Hello everyone

If I have a number say 10000 and I need to calculate in that way:
0 to 3000 >> 0%
3001 to 5000 >> 5%
5001 to 7000 >> 7.5%
7001 to any larger number >> 10%

so the output for this example would be: 0 + 100 + 150 + 200 = 550

[HansV]

Let's say the amount is in A1
I'd prefer to create a lookup list in a range of cells, but you will probably want a stand-alone formula

=SUMPRODUCT((A1>{3000,5000,7000})*(A1-{3000,5000,7000}),({0.05,0.075,0.1}-{0,0.05,0.075}))

[YasserKhalil]

Thank you very much my tutor
I didn't clarify the result well. I need to get the result for any number
Say I will enter the number in A1
The expected output would be:

B1......C1.........D1..........E1
0.......3000.......0% .........0
3001...5000......5%.........100
5001...7000.....7.5%.......150
7001...10000...10%.......,300
...............................550

I mean to get the whole steps and details for any number input

ِAnother example:
say A1 = 2000
B1......C1.........D1..........E1
0.......2000.......0% .........0

Another example:
Say A1 = 4000
B1......C1.........D1..........E1
0.......3000.......0% .........0
3001...4000......5%.........50

As the maximum number here is 2000 so we put 2000 not 3000 in the result

Thanks a lot

[HansV]

I'll leave it to you to work that out.

[YasserKhalil]

I have tried to find a proper way but I couldn't. And I trust in your solutions more than any other solution.

[HansV]

It's straightforward...

Yassir.xlsx

[YasserKhalil]

This is my try before having a look at your attachment

Code: Select all

Sub Test()
    Dim v, m As Long
    With ActiveSheet
        v = Range("A1").Value
        .Columns("B:E").ClearContents
        .Columns("B:D").NumberFormat = "@"
        If IsNumeric(v) And v > 0 Then
            .Range("B1").Value = 0
            If v > 3000 Then .Range("C1").Value = 3000 Else .Range("C1").Value = v
            .Range("D1").Value = "0%"
            .Range("E1").Value = 0
            If v > 3000 Then
                .Range("B2").Value = 3000
                If v > 5000 Then .Range("C2").Value = 5000 Else .Range("C2").Value = v
                .Range("D2").Value = "5%"
                .Range("E2").Value = (.Range("C2").Value - .Range("B2").Value) * 0.05
            End If
            If v > 5000 Then
                .Range("B3").Value = 5000
                If v > 7000 Then .Range("C3").Value = 7000 Else .Range("C3").Value = v
                .Range("D3").Value = "7.5%"
                .Range("E3").Value = (.Range("C3").Value - .Range("B3").Value) * 0.075
            End If
            If v > 7000 Then
                .Range("B4").Value = 7000
                .Range("C4").Value = v
                .Range("D4").Value = "10%"
                .Range("E4").Value = (.Range("C4").Value - .Range("B4").Value) * 0.1
            End If
            m = .Cells(Rows.Count, "E").End(xlUp).Row + 1
            .Range("E" & m).Formula = "=SUM(E1:E" & m - 1 & ")"
        End If
    End With
End Sub

[YasserKhalil]

Thank you very much for your great help, my tutor.