Can CF cells be counted ?

[serge]

Hi,

I would like to know if it's possible the count CF cells ?

Here in the table below K16:T25 I have numbers that read in column, those are data corresponding to each number that are above from K4:T4.

Then under " Selection " I have numbers that are the result of selection from another sheet.

Which highlight the numbers per row.

My question is in the row ( Yellow ) from K27:T27 can the CF be counted ? Can a formula do the job ?

For example from K16:K25 9 cells are highlighted

L16:L25 8 cells are highlighted

M16:M25 6 cells are highlighted Etc....

Thank you for any help.

Excel Workbook
	K	L	M	N	O	P	Q	R	S	T	U	V	BO	BP	BQ	BR	BS
4	1	2	3	4	5	6	7	8	9	10			Selection
15
16	1	2	3	4	5	6	7	8	9	10			1	2	3	4	5
17	44	52	23	16	62	36	99	12	15	18			23	36	44	52	62
18	22	79	26	13	15	88	37	56	75	36			13	15	22	26	36
19	19	20	18	22	17	23	16	24	15	25			20	19	22	18	23
20	34	33	32	31	30	20	21	22	23	24			24	30	31	32	33
21	19	18	20	17	22	23	16	24	25	15			15	16	17	18	19
22	20	22	19	23	18	24	25	17	16	15			22	23	18	25	20
23	20	22	16	15	23	19	18	17	24	25			24	17	15	20	.
24	16	20	24	15	19	22	18	23	17	25			23	16	20	22	19
25	22	20	24	25	16	15	19	18	17	23			24	19	18	20	22
26
27	9	8	6	7	6	3	3	3	1	3
Sheet

 

[Drrellik]

I asked a similar question months ago about counting cells with an interior color and I had to use VBA to get it done. So I would have to say no to just a formula you will probably have to create one in VBA

Please check this link for more information about creating your own user defined function for counting colored cells, note from what I have read, you will not be able to do this when using CF colors. rather it suggest to define your count using the same formula you used to create the CF.

hope this helps.

User Defined Excel functions to determine cell colors

~DR

 

[Michael M]

Drrellik is correct, you'll need VBA
Have a look here for the method and sample code

Sum/Count Cells By Fill Or Background Color in Excel

 

[serge]

Thank you guys I will try it

Serge.

 

[serge]

Maybe a formula can come to the same result using the data from the " Selection " table for each number 1 to 10 for the table on the left ?

For example the number 1 in K4.

it would use the data from the " selection " table and count

1 in BO16
44 in BQ17
22 in BQ18
19 in BP19
19 in BS21
20 in BS22
20 in BR23
16 in BP24
and 22 in BS25 ?

to avoid a VBA which I know nothing about it ?

Thank you.

 

[serge]

Anyone have an idea for my last post ?

 

[MARK858]

Not sure what you mean by post #5 (or can't be bothered to work out the layout) so dealing with your table in post #1 what is the conditional format rule for column K?

Can you also explain why K4 is green in the table but not counted?

 

[gaz_chops]

Not sure but try

=SUMPRODUCT(($BO$16:$BS$16=K16)+(K17=$BO$17:$BS$17)+(K18=$BO$18:$BS$18)+(K19=$BO$19:$BS$19)+(K20=$BO$20:$BS$20)+(K21=$BO$21:$BS$21)+(K22=$BO$22:$BS$22)+(K23=$BO$23:$BS$23)+(K24=$BO$24:$BS$24)+(K25=$BO$25:$BS$25))

 

[serge]

Gaz_chops,

Thank you so much, your formula works like a charm, I really appreciate your help.

Serge.

 

[gaz_chops]

You're welcome, just for info. if the cells are highlighted using a formula, then they can probably be counted using a formula.