SUMIF Based on Formatting

bjyes1

New Member
Joined
Feb 5, 2008
Messages
9
Can someone give the way to SUMIF a range of numbers based on formatting alone?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,099
Office Version
365
Platform
Windows
Excel formulae cannot see formatting, so it would need VBA.
If that is ok, then you will need to supply more info.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,437
Office Version
365
Platform
Windows
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
Joined
Feb 5, 2008
Messages
9
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
Joined
Jun 12, 2014
Messages
32,099
Office Version
365
Platform
Windows
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
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Ward</td><td style=";">Altitude</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Blackwater and Hawley</td><td style="text-align: right;;">65</td><td style="text-align: right;background-color: #FFFF00;;"></td><td style="text-align: right;;">392</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Cove and Southwood</td><td style="text-align: right;;">64</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Fernhill</td><td style="text-align: right;background-color: #FFFF00;;">62</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Knellwood</td><td style="text-align: right;;">74</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Manor Park</td><td style="text-align: right;;">101</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">North Town</td><td style="text-align: right;background-color: #FFFF00;;">71</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">North Town</td><td style="text-align: right;;">93</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Rowhill</td><td style="text-align: right;;">98</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">St John's</td><td style="text-align: right;;">71</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">St Mark's</td><td style="text-align: right;;">79</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">St Mark's</td><td style="text-align: right;;">70</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Wellington</td><td style="text-align: right;background-color: #FFFF00;;">98</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Wellington</td><td style="text-align: right;;">89</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">West Heath</td><td style="text-align: right;;">61</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Aldridge Central and South</td><td style="text-align: right;;">162</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Aldridge Central and South</td><td style="text-align: right;background-color: #FFFF00;;">161</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Aldridge Central and South</td><td style="text-align: right;;">165</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Brownhills</td><td style="text-align: right;;">156</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Brownhills</td><td style="text-align: right;;">153</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=bjyes(<font color="Blue">B2:B20,C2</font>)</td></tr></tbody></table></td></tr></table><br />
 

bjyes1

New Member
Joined
Feb 5, 2008
Messages
9
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

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

Worksheet Formulas
CellFormula
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,099
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
"Whatever's simplest and works is my motto"

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

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top