Insert Value "Number of cells with color filling" at the beginning of each row in the table

[apor]

Hello everyone,

I have a worksheet containing rows of data. cells in each row that meet a certain condition are highlighted yellow. Now i want to insert a cell at the beginning of the table with a number containing the number of cells that are highlighted yellow.

the conditional formatting string for the filling is as follows:

=AND(C2="",SUM($C2:C2)>0,SUM(C2:$CC2)>0)

i added the results i would like in column B as an Image

t

 

[Tetra201]

Try placing the following formula in cell B2 and drag-copying it down as needed:

=SUMPRODUCT(--(C2:CC2=""),SIGN(COUNTIF(OFFSET(C2,,,,COLUMN(C2:CC2)-COLUMN(C2)+1),">0")),SIGN(COUNTIF(OFFSET(CC2,,,,COLUMN(C2:CC2)-COLUMN(CC2)-1),">0")))

 

[Peter_SSs]

A couple more (non-volatile) options to consider. With your version I think that the column B formula would need to be confirmed with Ctrl+Shift+Enter, not just Enter, but the slightly longer column A formula I think could be entered normally with just Enter.

23 06 17.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	3	3	1		1	1			1
3	1	1	1	1	1		1	2	1
4	1	1			1	2		2	1
5	0	0	1	1	1	1
6	3	3		1				1	1
7	2	2	1	1			1
8	0	0	1	1	1	1	1	1
Count yellow

Cell Formulas
Range	Formula
A2:A8	A2	=LOOKUP(9^9,C2:CC2,COLUMN(C2:CC2)-COLUMN(C2)+1)-MATCH(TRUE,INDEX(C2:CC2<>"",0),0)+1-COUNT(C2:CC2)
B2:B8	B2	=LOOKUP(9^9,C2:CC2,COLUMN(C2:CC2)-COLUMN(C2)+1)-MATCH(TRUE,C2:CC2<>"",0)+1-COUNT(C2:CC2)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:M8	Expression	=AND(C2="",SUM($C2:C2)>0,SUM(C2:$CC2)>0)	text	NO

If it is possible that you could have rows with no numbers at all then the formulas would need to be modified like this
=IFNA(LOOKUP(9^9,C2:CC2,COLUMN(C2:CC2)-COLUMN(C2)+1)-MATCH(TRUE,C2:CC2<>"",0)+1-COUNT(C2:CC2),0)