# SUMIF Based on Formatting

#### bjyes1

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

#### Fluff

##### MrExcel MVP, Moderator
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

##### New Member
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?

##### MrExcel MVP
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

##### MrExcel MVP, Moderator
You can go with PaddyD's approach, or you could use a UDF like
Code:
``````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``````

#### bjyes1

##### New Member
You can go with PaddyD's approach, or you could use a UDF like
Code:
``````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``````
ABCD
1WardAltitude
2Blackwater and Hawley65392
3Cove and Southwood64
4Fernhill62
5Knellwood74
6Manor Park101
7North Town71
8North Town93
9Rowhill98
10St John's71
11St Mark's79
12St Mark's70
13Wellington98
14Wellington89
15West Heath61
16Aldridge Central and South162
17Aldridge Central and South161
18Aldridge Central and South165
19Brownhills156
20Brownhills153

</tbody>
Sheet1

Worksheet Formulas
CellFormula
D2=bjyes(B2:B20,C2)

</tbody>

<tbody>
</tbody>

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

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback

##### MrExcel MVP
"Whatever's simplest and works is my motto"

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

1,081,770
Messages
5,361,173
Members
400,617
Latest member
barron1

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...