Questions

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

+
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