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
30,545
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,177
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
30,545
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
30,545
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,078,467
Messages
5,340,495
Members
399,378
Latest member
voodoo1

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top