SUMIF Based on Formatting

[bjyes1]

Can someone give the way to SUMIF a range of numbers based on formatting alone?

 

[Fluff]

Excel formulae cannot see formatting, so it would need VBA.
If that is ok, then you will need to supply more info.

 

[Joe4]

Note that if the formatting you are referring to is the result of "Conditional Formatting", you should be able to apply the same logic to the SUMIF formula as you did in the Conditional Formatting rule to get what you want.

If the formatting is due to manual formatting changes, then you need to use VBA, as Fluff says.

 

[bjyes1]

So, VBA would be needed to, say, use the SUMIF formula to sum all cell with a certain format, say with a yellow shade? If so, how complex would the VBA code be? Is there guidance somewhere that helps with writing such code?

 

[PaddyD]

I wouldn't recommend it (i.e. vba is the way to go), but for completeness I'll point out that you could (sort of) use a formula approach here.

The technique is outlined here:

https://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html

..font colour is option 24. Come with some limitations, including:

- there are more font colours available than the 56 this formula will notice
- required file to be saved as xlm so 'feels' like you're using vba anyway

...only really advantage is you can get to what you're after without actually writing any code.

 

[Fluff]

You can go with PaddyD's approach, or you could use a UDF like

Function bjyes(Rng As Range, Clr As Range) As Double
   Dim Cl As Range
   
   For Each Cl In Rng
      If Cl.Interior.Color = Clr.Interior.Color Then
         bjyes = bjyes + Cl.Value
      End If
   Next Cl
End Function

Book1
	A	B	C	D
1	Ward	Altitude
2	Blackwater and Hawley	65		392
3	Cove and Southwood	64
4	Fernhill	62
5	Knellwood	74
6	Manor Park	101
7	North Town	71
8	North Town	93
9	Rowhill	98
10	St John's	71
11	St Mark's	79
12	St Mark's	70
13	Wellington	98
14	Wellington	89
15	West Heath	61
16	Aldridge Central and South	162
17	Aldridge Central and South	161
18	Aldridge Central and South	165
19	Brownhills	156
20	Brownhills	153
Sheet1

Cell Formulas
Range	Formula
D2	=bjyes(B2:B20,C2)

 

[bjyes1]

You can go with PaddyD's approach, or you could use a UDF like

Function bjyes(Rng As Range, Clr As Range) As Double
   Dim Cl As Range
   
   For Each Cl In Rng
      If Cl.Interior.Color = Clr.Interior.Color Then
         bjyes = bjyes + Cl.Value
      End If
   Next Cl
End Function

	A	B	C	D
1	Ward	Altitude
2	Blackwater and Hawley	65		392
3	Cove and Southwood	64
4	Fernhill	62
5	Knellwood	74
6	Manor Park	101
7	North Town	71
8	North Town	93
9	Rowhill	98
10	St John's	71
11	St Mark's	79
12	St Mark's	70
13	Wellington	98
14	Wellington	89
15	West Heath	61
16	Aldridge Central and South	162
17	Aldridge Central and South	161
18	Aldridge Central and South	165
19	Brownhills	156
20	Brownhills	153

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =bjyes(B2:B20,C2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Fluff and PaddyD,

thank you for your suggestions. 'Preciate your taking the time to respond. Both appear to be viable possibilities. Whatever's simplest and works is my motto. Thanks again.

--- bjyes1

 

[Fluff]

Glad we could help & thanks for the feedback

 

[PaddyD]

"Whatever's simplest and works is my motto"

That'll be Fluff's UDF by a long margin