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​

 

[Anfinsen]

Hey there, how do the cells become colored in the first place? Is there variable, or qualifying data that someone determines manually, and colors them manually? You could consider adding a helper column to determine this, or sumif based on those variables.

 

[offthelip]

I have written this UDf which is a function which sums by color , it has two inputs the first is a single cell which is colored and the second is a range of cells which you want to sum the cells with the same colotr as the first cell:

Function SumByColor(CellColor As Range, rRange As Range)
Application.Volatile
Dim cSum As Double

Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function

Put the code in a standard module in your workbook and you can use it like this:

=sumbycolor($K2,$G$9:$G$65)

 

[Anfinsen]

I have written this UDf which is a function which sums by color , it has two inputs the first is a single cell which is colored and the second is a range of cells which you want to sum the cells with the same colotr as the first cell:

Function SumByColor(CellColor As Range, rRange As Range)
Application.Volatile
Dim cSum As Double

Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.Sum(cl, cSum)
End If
Next cl
SumByColor = cSum
End Function

Put the code in a standard module in your workbook and you can use it like this:

=sumbycolor($K2,$G$9:$G$65)

I am not sure that answers my question. How does the cell get it color in the first place?

 

[offthelip]

with a slight modification does this do what you want:

Function SumByColoroff(CellColor As Range, rRange As Range, off As Long)
Application.Volatile
Dim cSum As Double

Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange

If cl.Interior.ColorIndex = ColIndex Then
tt = cl.Offset(off, 0)
cSum = WorksheetFunction.Sum(tt, cSum)
End If
Next cl
SumByColoroff = cSum
End Function

then in F5 you put:

=sumbycoloroff($B$4,B4:D4,ROW()-5)

and copy it down, This does rely on B4 being the color you wnat to sum up but you could put the color in any other cell and use that

 

[Fluff]

Try changing

=SUMIF(B5:D5,IsCellColored(B5:G5),B4:G4)

to

=SUMIF(B5:D5,IsCellColored(B5:D5),B$4:D$4)

 

[Antonescu]

with a slight modification does this do what you want:

Function SumByColoroff(CellColor As Range, rRange As Range, off As Long)
Application.Volatile
Dim cSum As Double

Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange

If cl.Interior.ColorIndex = ColIndex Then
tt = cl.Offset(off, 0)
cSum = WorksheetFunction.Sum(tt, cSum)
End If
Next cl
SumByColoroff = cSum
End Function

then in F5 you put:

=sumbycoloroff($B$4,B4:D4,ROW()-5)

and copy it down, This does rely on B4 being the color you wnat to sum up but you could put the color in any other cell and use that

with a slight modification does this do what you want:

Function SumByColoroff(CellColor As Range, rRange As Range, off As Long)
Application.Volatile
Dim cSum As Double

Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange

If cl.Interior.ColorIndex = ColIndex Then
tt = cl.Offset(off, 0)
cSum = WorksheetFunction.Sum(tt, cSum)
End If
Next cl
SumByColoroff = cSum
End Function

then in F5 you put:

=sumbycoloroff($B$4,B4:D4,ROW()-5)

and copy it down, This does rely on B4 being the color you wnat to sum up but you could put the color in any other cell and use that

Thanks, it does return 17 for ROW 5 (Search), but for all the other rows it returns 0. I tried changing the formula, for example for ROW 6 =sumbycoloroff($B$4,B4:D4,ROW()-6), but it just returns 17 again, although it should be 14. Maybe I didn't understand the formula.

 

[Antonescu]

Hey there, how do the cells become colored in the first place? Is there variable, or qualifying data that someone determines manually, and colors them manually? You could consider adding a helper column to determine this, or sumif based on those variables.

I mentioned it's NOT conditional formatting. It's done manually. Thanks!

 

[Antonescu]

Try changing

=SUMIF(B5:D5,IsCellColored(B5:G5),B4:G4)

to

=SUMIF(B5:D5,IsCellColored(B5:D5),B$4:D$4)

Thanks! I tried, but the formula spills, just like it does without the $

 

[Fluff]

but the formula spills, just like it does without the $

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)