SUMIF for colored cells

[Antonescu]

Hello!
I am trying to make a media plan template, but I've run into a dead end. I need to 1. add the sum of the days in a month a channel is active and 2. make a budget per week.
Unfortunately, the xl2bb add-in doesn't work on my end
I am pasting below:

• the worksheet
• a screenshot, where the colored cells are visible
• The Function for IsCellColored, which is NOT by Conditional Formatting, just plain color
  • 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

• What I would need is:
  
  F5:F10 should have: Sum of the Days when the campaign is active. Something like F5=SUMIF(B5:D5,IsCellColored(B5:G5),B4:G4), but this one doesn't work

I inserted the desired results by hand. Sorry if the explanations are not very clear, I tried simplifying as much as possible. (The budget per week is already calculated (B11 = =IF(SUM(IF((IsCellColored(B5:B10))*($F5:$F10<>0),$E5:$E10/$F5:$F10*B$4))=0,"",SUM(IF((IsCellColored(B5:B10))*($F5:$F10<>0),$E5:$E10/$F5:$F10*B$4)))

Thank you so much!!!

	MAY			BUDGET	Active Days
Week	15-21	22-28	29-31
DAYS	7	7	3
Search				€ 8,000​	17​
Google Display				€ 6,000​	14​
Facebook				€ 3,500​	7​
Insta				€ 7,000​	10​
TikTok				€ 3,500​	10​
OOH					0​
BUDGET PER WEEK	€ 12,244​	€ 11,194​	€ 4,562​	€ 28,000​

 

[Antonescu]

You never actually said that, you just said it didn't work.
try it like

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

and

=SUM(IsCellColored(B5:D5)*$B$4:$D$4)

OMG, it works!!!! Thank you so much!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Antonescu]

Glad we could help & thanks for the feedback.

Can I please ask you for one more thing? It seems there's something wrong with B11:D11 and I can't figure it out. They should show the Budget per Week. So, B11 should be IF IsCellColored(B5:B10), E5:E10 / F5:F10 * C4, but only if the cells are colored, of course.

 

[Fluff]

As that's a different question, it needs a new thread.