Sum of a division IF cells are colored

[Antonescu]

Hi guys,

I have been struggling for half a day with a formula and I can't get my head around it.
I have to make a media calendar and calculate the budget / week (F10). But not all media channels are active on all the weeks. So, I want a function to SUM budgets only for the channels that are active (if the cells are colored). The budget per channel being Total channel budget Divided by number of weeks. I have the code for counting colored cells. But my function returns SUM as if all the cells were colored. I verified in column AI. Not sure how clear I was, so I'll attach a picture

Could you please give me a hand?

Thanks a lot!

 

[awoohaw]

Are you using conditional formatting rules to color the cells?

Also, can you post a sample of your worksheet using the the xl2bb add in (Link Below). Images are not very helpful in terms of building your scenario in a workbook. If you can't use xl2bb, then please post the data as a table, but please also paste any formulas (and dependent values) that are pertinent to the cells in question.

Thanks in advance.

 

[Georgiboy]

I used the below function:

Function InteriorColor(CellColor As Range)
    Application.Volatile
    InteriorColor = CellColor.Interior.ColorIndex
End Function

And a dynamic array formula as below:

Book1
	A	B	C	D
1	1		Red Sum	3
2	1
3	1
4	1
5	1
6	1
7	1
8	1
9	1
10	1
Sheet1

Cell Formulas
Range	Formula
D1	D1	=SUM(LET(c,BYROW(A1:A10,LAMBDA(x,InteriorColor(x))),FILTER(A1:A10,c=3,"")))

 

[Antonescu]

Unfortunately, I couldn't make the xl2bb buttons work, they just stay gray, although I even disabled Protected View. Sorry

1. I simplified the table as much as possible
2. Inserted the new screenshot to view which cells are colored
3. E3 =CountColoredCells(B3:C3) and same down to E9
4. F3 =D3/F3 same down to F9
5. B10 =SUM(IF((IsCellColored(B3:B9))*(D3:D9<>0),D3:D9/E3:E9))
6. C10 =SUM(IF((IsCellColored(C3:C9))*(D3:D9<>0),D3:D9/E3:E9))
7. CountColoredCells module is

Function CountColoredCells(rData As Range) As Long
Dim rCell As Range
Dim lColoredCells As Long
lColoredCells = 0
For Each rCell In rData
If rCell.Interior.ColorIndex <> xlNone Then lColoredCells = lColoredCells + 1
Next rCell
CountColoredCells = lColoredCells
End Function

8. IsCellColored module is:
Function IsCellColored(CellRange As Range) As Variant
Dim Result() As Variant
ReDim Result(1 To CellRange.Cells.Count)
Dim i As Integer
i = 1
For Each rCell In CellRange
Result(i) = (rCell.Interior.ColorIndex <> xlNone)
i = i + 1
Next rCell
IsCellColored = Result
End Function

		MAI	BUGET	Weeks	Budget / Week
	24-30aprilie	1-7mai
Search			€ 8,000	2​	€ 4,000
GDN			€ 6,000	1​	€ 6,000
Facebook			€ 3,500	1​	€ 3,500
Insta			€ 7,000	1​	€ 7,000
TikTok			€ 3,500	1​	€ 3,500
Snapchat			€ 22,000	1​	€ 22,000
			€ 18,000	1​	€ 18,000
TOTAL	€ 64,000	€ 448,000	€ 68,000	8​	€ 64,000

 

[Antonescu]

Are you using conditional formatting rules to color the cells?

Also, can you post a sample of your worksheet using the the xl2bb add in (Link Below). Images are not very helpful in terms of building your scenario in a workbook. If you can't use xl2bb, then please post the data as a table, but please also paste any formulas (and dependent values) that are pertinent to the cells in question.

Thanks in advance.

No, just regular color, no conditional formatting. thanks!

 

[Antonescu]

@Georgiboy, thank you, the counting colored cells part I got with the code I pasted in the reply above, it's that sum that still beats me.

 

[Fluff]

Try it like

Function IsCellColored(CellRange As Range) As Variant
Dim Result() As Variant
ReDim Result(1 To CellRange.Cells.Count, 1 To 1)
Dim i As Integer
i = 1
For Each rCell In CellRange
Result(i, 1) = (rCell.Interior.ColorIndex <> xlNone)
i = i + 1
Next rCell
IsCellColored = Result
End Function

 

[Antonescu]

Try it like

Function IsCellColored(CellRange As Range) As Variant
Dim Result() As Variant
ReDim Result(1 To CellRange.Cells.Count, 1 To 1)
Dim i As Integer
i = 1
For Each rCell In CellRange
Result(i, 1) = (rCell.Interior.ColorIndex <> xlNone)
i = i + 1
Next rCell
IsCellColored = Result
End Function

OMG, it worked, thank you sooo much, you don't know how much I appreciate it

 

[Fluff]

Glad we could help & thanks for the feedback.