sum if bold or italics with condition

[spilner]

hi,heres the code.

=Sumifbold(F11:F72,range>3000,3000,"value false"))

can someone write a vba for this?

thanks a lot

 

[spilner]

the code i found is this but its just sum if bold but i want with condition value if true and false

Function SumIfBold(MyRange As Range) As Double

    Dim cell As Range
    For Each cell In MyRange
        If cell.Font.Bold = True Then
            SumIfBold = SumIfBold + cell
        End If
    Next cell

End Function

 

[VoG]

Like this?

Function SumIfBold(MyRange As Range) As Double

    Dim cell As Range
    For Each cell In MyRange
        If cell.Font.Bold And cell.Value > 3000 Then
            SumIfBold = SumIfBold + cell.Value
        End If
    Next cell

End Function

 

[spilner]

its not working..

i want
=Sumifbold(F11:F72,IF(F11:F72>3000,3000,""))

if the range is more than 3000 then 3000.

thanks for your reply

 

[VoG]

Try

Function SumIfBold(MyRange As Range) As Double

    Dim cell As Range
    For Each cell In MyRange
        If cell.Font.Bold Then
            SumIfBold = SumIfBold + WorksheetFunction.Min(cell.Value, 3000)
        End If
    Next cell

End Function

 

[spilner]

the code is working but not sum all the lower 3000.can u write somethin so the code sum all the lower than 3000 too.

somethin like this =sumifbold(f11:f72,MIN(3000,F11),F11)

thanks man.almost done now..

 

[VoG]

That will sum all bold cells in the range, with a cap of 3000. If that is not what you want you need to explain in English, not pseudo code.

 

[spilner]

i want to sum all in the range include bold but all bold cells will take 3000 only.
the last code u did.it sum only the bold.it not sum all lower than 3000.
sorry for my bad english.

 

[spilner]

can u help me with this pls? its almost done. just need to write somethin so it will count the unbold too.thanks a lot vog

 

[Scott Huish]

Do you need it to sum for each number in the range, that is less than 3000 and bold or do you need it to sum for each number that is in bold in the range with the value that is in F11 with a cap of 3000?

If the latter,

Function SumIfBold(r As Range, SumNumber As Double) As Double
Dim c As Range, s As Double
Application.Volatile
For Each c In r
    If c.Font.Bold Then s = s + SumNumber
Next
SumIfBold = s
End Function

Then write your formula like this:
=sumifbold(F11:F72,MIN(3000,F11))