-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 74,140
- 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.
If the formatting is due to manual formatting changes, then you need to use VBA, as Fluff says.
Upvote
0
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.
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.
Upvote
0
Fluff
MrExcel MVP, Moderator
- Joined
- Jun 12, 2014
- Messages
- 92,717
- 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| 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) | |
Upvote
0
