IF criteria - cell characteristics

%3CLINGO-SUB%20id%3D%22lingo-sub-2798012%22%20slang%3D%22en-US%22%3EIF%20criteria%20-%20cell%20characteristics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2798012%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20is%20there%20a%20possibility%20in%20Excel%20to%20%22sumif%22%20or%20%22countif%22%20and%20the%20criteria%20is%20e.g.%20bold%20letters.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%2264%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3E%3CSTRONG%3E1%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CSTRONG%3E4%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CSTRONG%3E5%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3ESo%2C%20in%20this%20case%20it%20should%20only%20sum%20numbers%20which%20are%20bold%20type%20(1%2B4%2B5).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2798012%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2798280%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20criteria%20-%20cell%20characteristics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2798280%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1170898%22%20target%3D%22_blank%22%3E%40davidbutov%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENo%2C%20you'd%20need%20VBA%20for%20that.%20Here%20is%20a%20user-defined%20function%20that%20you%20can%20copy%20into%20a%20module%20in%20the%20Visual%20Basic%20Editor%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EFunction%20SumBold(rng%20As%20Range)%20As%20Double%0A%20%20%20%20Dim%20cel%20As%20Range%0A%20%20%20%20Application.Volatile%0A%20%20%20%20For%20Each%20cel%20In%20rng%0A%20%20%20%20%20%20%20%20If%20cel.Font.Bold%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20SumBold%20%3D%20Application.Sum(SumBold%2C%20cel.Value)%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20cel%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EYou%20can%20then%20use%20it%20in%20a%20formula%20such%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DSumBold(A1%3AA10)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EYou'll%20have%20to%20save%20the%20workbook%20as%20a%20macro-enabled%20workbook%20and%20allow%20macros%20when%20you%20open%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWarning%3A%20the%20formula%20result%20will%20not%20be%20updated%20automatically%20when%20you%20toggle%20the%20Bold%20property%20of%20a%20cell.%20It%20will%20be%20updated%20the%20next%20time%20Excel%20recalculates%20any%20formula.%20You%20can%20press%20F9%20to%20update%20it%20immediately.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, is there a possibility in Excel to "sumif" or "countif" and the criteria is e.g. bold letters.

 

1
2
4
5
3
2
5
6

So, in this case it should only sum numbers which are bold type (1+4+5).

3 Replies

@davidbutov 

No, you'd need VBA for that. Here is a user-defined function that you can copy into a module in the Visual Basic Editor:

Function SumBold(rng As Range) As Double
    Dim cel As Range
    Application.Volatile
    For Each cel In rng
        If cel.Font.Bold Then
            SumBold = Application.Sum(SumBold, cel.Value)
        End If
    Next cel
End Function

You can then use it in a formula such as

=SumBold(A1:A10)

You'll have to save the workbook as a macro-enabled workbook and allow macros when you open it.

 

Warning: the formula result will not be updated automatically when you toggle the Bold property of a cell. It will be updated the next time Excel recalculates any formula. You can press F9 to update it immediately.

@Hans Vogelaar 

 

Thank you very much, I think this will help me!

Have a nice day!

@davidbutov 

Here is another example without VBA

Sum of all bold cell values

 

Go to: Formulas -> "Define Name"

Set a custom name:

Insert -> Name -> Define -> Name in the table: 'Bold' ->

Refers to:

English:

= ASSIGN.CELL(20,Sheet1!$A1)*1

German:

=ZELLE.ZUORDNEN(20;Tabelle1!$A1)*1

(In the inserted example file you will find the correct settings for the table under the name manager.)

 

Now you can use the following column in any auxiliary column (here column B)

Enter formula:

= Bold

And get a 1 if the cell in the same row in column A is bold

is formatted.

 

Now you can use SUMMIF () to calculate the sum:

= SUMIF(B:B,1,A:A)

 

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.