how to count and sum cells by color in excel

[Patriot2879]

Hi I have the formula below where I am trying to count how many cells have a colour and counting, where my range is to a and the cell colour is yellow which is p1, but the code doesn't work I get a #NAME ? please can u help?

=ASAPCOUNTBYCELLCOLOR(A:A,P1)

 

[Sam_D_Ben]

If its for summing up,

Public Function ASAPSumByCellColor(rngSource As Range, sColorIndex As Single) As Single                                   
                                   
    Dim rngCel As Range
    
  Application.Volatile
  N = 0
  For Each rngCel In rngSource
    If rngCel.Interior.colorindex = sColorIndex Then
      If IsNumeric(rngCel.Value) Then N = N + rngCel.Value
    End If
  Next
  ASAPSumByCellColor = N
End Function

<tbody></tbody>

Excel 2013/2016
	A	B	C	D	E
1
2	35	47		3	168
3	65	77
4	43	55
5	13
6	14
7	29
Sheet4

Cell Formulas
Range	Formula
E2	=ASAPSumByCellColor($A$2:$B$7,D2)

 

[Patriot2879]

Hi thanks for your help. But unfortunately it was to count colours on cells. I managed to get the code for the asap formula but it slowed the spreadsheet down alot so I deleted that formula/code now as no good for me.

 

[Fluff]

How are the cells getting coloured?

 

[Patriot2879]

Hi I just click on the cell then fill cell with a colour. I haven't used conditional formatting.

 

[Fluff]

Is there anything in those cells to indicate what colour it should be?

 

[Patriot2879]

Hi. No nothing. For example all it is I run a report and highlight the line if important yo make it stand out

 

[Fluff]

In that case you will need a macro or the UDF to do what you want, but it won't update automatically because Xl does not recognise the colour of a cell being changed.
You would be better of putting something like an "X" in cell as well as changing the colour. That way you could use a normal countif formula.

 

[Patriot2879]

Hi thanks for the advice. I think I will go with your idea much easier. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.
Using colour as data is something that's best avoided as it can cause all sorts of problems.

 

[Patriot2879]

Thank you. Your feedback and help is always fantastic I shall definitely do what you recommend.