Questions

if statement microsoft excel- if column a has bold text then sum column c

Tags:
+
0 Votes
Locked

if statement microsoft excel- if column a has bold text then sum column c

kmbrenne
Hi,

Here is what I am trying to do!

If the text in Column A1:A45 is bold, then sum the corresponding rows in Column C1:C45.

So if Cell A1's text is bold, A5 is bold and A7 is bold, then add the value of cell C1, C5 and C7 together. Make sense?? THanks so much!
  • +
    1 Votes
    smuzoen

    If there is some condition that makes the cells bold then perhaps you could use formulas however if the cells are manually made bold you are stuck using a UDF. In a module place the following code
    [pre]
    Function sumBoldCells(sumRng As Range) As Double

    Dim ifBold As Range
    For Each ifBold In sumRng
    If ifBold.Offset(, -2).Font.Bold = True Then
    sumBoldCells = sumBoldCells + ifBold
    End If
    Next
    End Function
    [/pre]
    Then to use the formula-> =sumBoldCells(C1:C45)

    +
    0 Votes
    kmbrenne

    I appreciate you taking the time to help me, so thank you! I tried doing this but it keeps shooting out a Name? error at me. Any idea why?

    +
    0 Votes
    Charles Bundy

    The code works as advertised under Excel 2010.

    If that is the version of excel you are using hit [ALT][F11] to get to the VBA editor the select Module from the Insert menu. Type the code in, flip back to your worksheet and assuming you have values in A & C you should be able to go to an empty cell and type =sumBoldCells(C1:C45). The only caveat is no auto refresh. That will be left as an exercise for the student...

    +
    0 Votes
    cliffbeacham

    While you can do this with VBA I would just use a SUMIFS() and a helper column (it is a lot simpler). Put a 1 in the helper column next to the required amounts and then 'sum' the amts using SUMIFS(). This has the advantage that you can use the technique at any time (you don't have to have your code available) using a built-in Excel formula. Also you can have more than 1 group because you are not just using a True/False (only 2 outcomes). Also you do not have to alter the formating (although you could).

    +
    0 Votes
    kmbrenne

    @Charles, I unfortunately am using excel 2007. Is that why it is not working?

    @Cliff. That is exactly what I ended up doing. I just couldn't wrap my head around how to do if statements with bold font vs values. I thought it would be a good time to learn. Thanks again everyone!

    +
    0 Votes
    Charles Bundy

    on where/when it pops up. Are you in the VB editor or in the worksheet? I really don't think the difference between 2007 and 2010 would matter wrt code...

    +
    0 Votes
    kmbrenne

    The error occurs in the worksheet. Everything seems good in the VB editor

  • +
    1 Votes
    smuzoen

    If there is some condition that makes the cells bold then perhaps you could use formulas however if the cells are manually made bold you are stuck using a UDF. In a module place the following code
    [pre]
    Function sumBoldCells(sumRng As Range) As Double

    Dim ifBold As Range
    For Each ifBold In sumRng
    If ifBold.Offset(, -2).Font.Bold = True Then
    sumBoldCells = sumBoldCells + ifBold
    End If
    Next
    End Function
    [/pre]
    Then to use the formula-> =sumBoldCells(C1:C45)

    +
    0 Votes
    kmbrenne

    I appreciate you taking the time to help me, so thank you! I tried doing this but it keeps shooting out a Name? error at me. Any idea why?

    +
    0 Votes
    Charles Bundy

    The code works as advertised under Excel 2010.

    If that is the version of excel you are using hit [ALT][F11] to get to the VBA editor the select Module from the Insert menu. Type the code in, flip back to your worksheet and assuming you have values in A & C you should be able to go to an empty cell and type =sumBoldCells(C1:C45). The only caveat is no auto refresh. That will be left as an exercise for the student...

    +
    0 Votes
    cliffbeacham

    While you can do this with VBA I would just use a SUMIFS() and a helper column (it is a lot simpler). Put a 1 in the helper column next to the required amounts and then 'sum' the amts using SUMIFS(). This has the advantage that you can use the technique at any time (you don't have to have your code available) using a built-in Excel formula. Also you can have more than 1 group because you are not just using a True/False (only 2 outcomes). Also you do not have to alter the formating (although you could).

    +
    0 Votes
    kmbrenne

    @Charles, I unfortunately am using excel 2007. Is that why it is not working?

    @Cliff. That is exactly what I ended up doing. I just couldn't wrap my head around how to do if statements with bold font vs values. I thought it would be a good time to learn. Thanks again everyone!

    +
    0 Votes
    Charles Bundy

    on where/when it pops up. Are you in the VB editor or in the worksheet? I really don't think the difference between 2007 and 2010 would matter wrt code...

    +
    0 Votes
    kmbrenne

    The error occurs in the worksheet. Everything seems good in the VB editor