Forum Discussion

KevinG44's avatar
KevinG44
Copper Contributor
Sep 09, 2020

Adding numbers based on font size

I have a list of bond payments. The ones that come in on the first of the month are in font size 10....the ones that pay on the 15th are in font size 12. Is there a way for me to create a formula that will add the values of only one particular font size?  Thanks for any help.

excel
Formulas and Functions

2 Replies

  • JMB17's avatar
    JMB17
    Bronze Contributor
    Sep 09, 2020
    Not without using vba to write a procedure (or a user defined function that you could use in the worksheet).

    Could you use a conditional sum function to test the day of the date and sum amounts based on whether it is 1 or 15?

    Say the date is in column A and the amount is in B:
    =SUMPRODUCT(--(DAY(A1:A10)=1),B1:B10)
    =SUMPRODUCT(--(DAY(A1:A10)=15),B1:B10)
  • Bennadeau's avatar
    Bennadeau
    Iron Contributor
    Sep 09, 2020

    Hi KevinG44,

    You can use the following VBA code.

    Private Sub count_font()
Dim i As Integer
Dim calc15 As Integer
Dim font15 As Integer
Dim j As Integer
Dim calc10 As Integer
Dim font10 As Integer

calc = 0
'loops through the cells in column 1
For i = 1 To 8
'gets the font size
font15 = Range(Cells(i, 1), _
Cells(i, 1)).Font.Size
If font15 = 15 Then
calc15 = calc15 + Cells(i, 1).Value
End If
Next i
Cells(2, 3) = "Total for Font 15 - " & calc15

calc10 = 0
'loops through the cells in column 1
For j = 1 To 8
'gets the font size
font10 = Range(Cells(j, 1), _
Cells(j, 1)).Font.Size
If font10 = 10 Then
calc10 = calc10 + Cells(j, 1).Value
End If
Next j
Cells(3, 3) = "Total for Font 10 - " & calc10


End Sub

    You will need to adjust it for where your data is actually written in your spreadsheet and where you want the output to go. Let me know if you need a hand with that.

     

Resources