Formulas Based on Cell Color - SUMIFS For Calendar Issue

0

Hello, I am hoping you can help me.  I found your tutorial "Formulas Based on Cell Color - SUMIFS, IF, COUNTIF, Etc. - TeachExcel.com", which is awesome!!

I am trying to do this color function, but for a time off calendar. 

  • The goal is to be able to add color to the cells with the dates a person is taking off. 
  • Then add the time off hours based on the color. 

I have a legend with the colors and code.  I am not sure how to do the SUMIFS with taking the color coding from a legend and the data from the calendar grid? 

Please help.  :)

Sincerely, Destiny

Answer
Discuss

Answers

0
Selected Answer

Destiny

The answer from Variatus is neat and perhaps your best option. This answer works too and might help you understand the tutorial better (or the part I looked at)...

If you look at my version of your file (attached), try using Format Painter to copy the colour of a green cell (K4) to Z10 say then type a number in Z10 (or copy K4 to 10). The Vacations total cell AM10 will change to suit. Works for your other colours or date cells, provided the correct colour is in the cell with a number..

That's because I've added a simple macro (using a named range) and created the appropriate SUMIF formula. To understand that, see below...

In your *.xlsx file, sample cell AM4 formula is =SUMIFS(B4:AL4,AM28:AM30,65280) which means sum the numbers in B4:AL4 if a corresponding cell in AM28:AM30 is ? (you put 65280  for the colour green rather than = 65280) but even that doesn't work.

The tutorial Sumifs formula relied on a cell next to the summed cell contain a number representing the colour. In my file, I've added (hidden) below the month rows (e.g. hidden row 5 holds the colours for individual date hours cells in row 4 January).

This macro in the file senses when a number is changed in any hours cells then puts the cell colour value in the hidden cell below - note that the Offset is 1,0 i.e. 1 row down, 0 columns across (rather than 0,1 for one column right in the tutorial):

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, ActiveSheet.Range("DayColors")) Is Nothing Then

    For Each jCell In Target
    jCell.Offset(1, 0).Value = jCell.Interior.Color
    Next jCell

End If

End Sub

REVISION 1: The code above (and attached file) are changed so it works if you  copy/paste of several cells at once. The For Each...Next loop does the colour value calculation for how ever many cells are copy/pasted in the hours row.

The code is simple because I've used Name Manager (under the Formulas ribbon) to define a named range called "DayColors" as the hours cells in B4:AL4 plus B8:AL8 etc.

Now it's easy to create the formula to add those values. E.g. the January vacations total is:

=SUMIF(B5:AL5,"=65280",B4:AL4)

It's a SUMIF (not SUMIFS) since there only one criteria and means if a value in (hidden) cells B5:AL5 equals 65280 (the colour value added by the macro) then add the corresponding value in the row above B4:AL4 to the sum.

I leave you to see how the same is done for Sick and Personal time (already done in my file).

That's it!

Discuss

Discussion

John_Ru,

Thank you so much for the answers and file provided.  This works perfectly and much cleaner than my solution. lolol  

Grateful for your time and information provided.

Destiny
Destiny_s_Dragon (rep: 2) Dec 17, '20 at 1:31 pm
Add to Discussion
0

I don't know the tutorial you have tried to follow but this UDF will do what you want in your workbook, in this project..

Option Explicit

Function SumByColor(SumRng As Range, _
                    Clm As Long) As Double
    ' 142

    Const ColorSampleRow    As Long = 2         ' change to suit

    Dim Fun                 As Double           ' function return value
    Dim Col                 As Long             ' sample color
    Dim Cell                As Range            ' loop counter: cells in SumRng

    Col = Cells(ColorSampleRow, Clm).Interior.Color
    For Each Cell In SumRng
        If Cell.Interior.Color = Col Then Fun = Fun + Val(Cell.Value)
    Next Cell

    SumByColor = Fun
End Function

Install the function in a standard code module, by default Module1 (but it's recommended to give it your own, meaningful name). In your calendar, place this function call in cell AM4. You can copy it from there to AN4:AO4 and then AM4:AO4 to AM6:AO6 etc.

=SumByColor($B4:$AL4,COLUMN())

In this function call $B4:$AL4 is the range you want to sum and COLUMN() feeds the number of the column to the function in which the formula resides. The function has the constant ColorSampleRow (currently set to row 2) so that the coordinates of the COLUMN() argument and the row constant tell the function where the sample color is. The function then looks at each cell in the sum range and adds its value to a total if the colours match.

Discuss

Discussion

@Variatus- good to see our recent chat on SumByColor has another  use :-)
John_Ru (rep: 2147) Dec 17, '20 at 5:38 am
Variatus, 

Thank you so much for the answers.  I will try this out and see if it works.  I did manage an solve the issue, in a probably longer format than what the codes seems would do.  

Grateful for your time and information provided.

Destiny
Destiny_s_Dragon (rep: 2) Dec 17, '20 at 1:23 pm
I'm glad you will still try my suggestion. It works on the workbook you posted with your question. It doesn't need sample colour cells because it uses the header cell as sample. For this reason it doesn't use colour numbers, either. It doesn't need instructions to the user, and its function call is both shorter and simpler.
The really admirable thing is that you found a solution on your own. Bear in mind that your solution is based on the method you got from the tutorial, while mine is based on your problem. Comparing apples with oranges, really.
Variatus (rep: 4864) Dec 17, '20 at 7:14 pm
Add to Discussion
0

Here is the solution I came up with based on the tutorial:

SECRET Excel Function to Sum and Count by Cell Colour - YouTube

I created the color numbers below each row where the colored time off would be added.  then added the rows similarly to how the tutorial showed.  But I had to do this for each month row.  

Solution # 2 above shows the result without having to add a row of hidden calculations.  Much simpler than my initial solution. 

Thank you and have a happy holiday season.  Stay Safe!

Destiny

Discuss

Discussion

Destiny. Good to see you have a solution to suit you. Hopefully you learned some things in doing do.
As Don said the other day (I think), there are normally lots of ways to do something in Excel.

Have a great holiday (when it arrives) 
John_Ru (rep: 2147) Dec 17, '20 at 2:35 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login