1

I have the following formula in "Summary" worksheet starting in cell F4:F13...

=COUNTIFS('Master Matrix'!L:L,A4,'Master Matrix'!M:M,"Received")

Of course the "A4" in the formula changes to "A5" when the formula is in F5, etc.

What I would like is something like..

=IF(mycolor<>16,COUNTIFS('Master Matrix'!L:L,A4,'Master Matrix'!M:M,"Received"))

"mycolor<>16" should mean if the background color is not "Color 16", grey, #808080, or RGB 128,128,128, then do the COUNTIFS.

Can someone help me figure this out? Do I need a VBA function and\or a named range to get this to work?

KoreanGuy
  • 37
  • 1
  • 6
  • Yeah, you're going to need VBA - Excel Formulas can't be used to determine a cell color. Do you know any VBA? If so, would you mind trying to get as far as you can, then let us know what other help? You'll be wanting to use `.Cells(1,1).interior.colorindex` to get the interior color. – BruceWayne Aug 10 '15 at 17:13
  • 2
    @BruceWayne: That is not true :) You may want to see [THIS](http://stackoverflow.com/questions/20489472/how-to-count-cells-in-a-range-with-a-value-less-than-another-cell-in-excel/20491479#20491479) – Siddharth Rout Aug 10 '15 at 17:19
  • @SiddharthRout O_O. Wow, I had no idea - pretty rad. I'll be looking through that for sure. – BruceWayne Aug 10 '15 at 17:46

1 Answers1

0

If you don't want to do too much VBA, you can create a custom function. The following function will be callable in a spreadsheet and take two parameters. The first parameter is the color index and the second parameter is the cell you want to reference.)

Add this to a new module in your workbook and it should be available to the entire workbook.

Public Function IsCellColor(ByVal ColorIndex As Integer, Optional ByRef Reference As Range)

    If Reference Is Nothing Then Set Reference = ActiveCell

    If Reference.Interior.ColorIndex = ColorIndex Then
        IsCellColor = True
    Else
        IsCellColor = False
    End If

End Function
Cohan
  • 4,384
  • 2
  • 22
  • 40