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

 

[Scott Huish]

If the former:

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

Then write your formula like this:

=SUMIFBOLD(F12:F72)

 

[spilner]

in the range f11:f72 example

5000
2000
10
5
600
500
1

i want it count all in the range except the bold only will take 3000 from 5000.
the total will be 6116.
which more than >3000.i will bold it.so,it take 3000 only.the one without bold,it will sum too.
let me know if u dont understand.sorry for my bad english.

 

[spilner]

the VOG's code is working fine.just need to add the code that will sum without the bold too.

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</pre>

 

[Scott Huish]

Why don't you just use a regular formula then?

=SUM(IF(F11:F72>3000,3000,F11:F72))

Confirm with CTRL-SHIFT-ENTER rather than just Enter

 

[spilner]

the reason is sometime i will count the value more than 3000.

example :

7000
5000
2000
10
5
600
500
1
-----
13116

can u write the vba code.the vog's code is almost done.just need to add it sum the unbold too.thanks VOG & PEPPER

 

[VoG]

Maybe

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)
        Else
            SumIfBold = SumIfBold + cell.Value
        End If
    Next cell

End Function

 

[spilner]

Perfecttttt...i know u did it vog...thank you so much vog & hotpepper.. You guys the best...