User Defined Function to count number cells

[kayza]

Hi, I want to count the number of selected cells in a range, then convert them to a value of 0 and 1.

	A	B	C	D	E	F	G
1	Data 1	Data 2	Data 3	Data 4			Result
2	Apple	Banana					1100
3		Orange		Lemon			0101
4			Grape				0010

So far, the code I can work with is like this. but the results are not as expected

Function Tot(RR As Range)
Dim cell As Range
Tot = "0"
For Each cell In RR
If cell.Value = "" Then
    Tot = Tot & 1
End If
Next cell
End Function

Thank you in advance

 

[Fluff]

What version of Excel do you have?
Please update you account details to show this, as it affects which functions you can use.

 

[lrobbo314]

How about this?

Book1
	A	B	C	D	E	F	G	H
1	Data 1	Data 2	Data 3	Data 4			Result	Array Formula
2	Apple	Banana					1100	1100
3		Orange		Lemon			0101	0101
4			Grape				0010	1011
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=TEXT(DEC2BIN(SUMPRODUCT((TRANSPOSE(A2:D2)=0)*(2^(ROW(INDIRECT("1:"&COLUMNS(A2:D2)))-1)))),"0000")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Fluff]

Two other options

+Fluff v2.xlsm
	A	B	C	D	E	F	G
1	Data 1	Data 2	Data 3	Data 4		UDF	Formula
2	Apple	Banana				1100	1100
3		Orange		Lemon		0101	0101
4			Grape			0010	0010
Work

Cell Formulas
Range	Formula
F2:F4	F2	=kayza(A2:D2)
G2:G4	G2	=TEXTJOIN(,,IF(A2:D2="",0,1))

Function kayza(Rng As Range) As String
   Dim Cl As Range
   
   For Each Cl In Rng
      kayza = kayza & IIf(Cl = "", 0, 1)
   Next Cl
End Function

 

[lrobbo314]

I noticed the original formula I posted didn't produce correct results for each of the rows. This one seems to do it correctly.

Book1
	A	B	C	D	E	F	G	H
1	Data 1	Data 2	Data 3	Data 4			Result	Array Formula
2	Apple	Banana					1100	1100
3		Orange		Lemon			0101	0101
4			Grape				0010	0010
5		Apple	Banana					0110
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=TEXT(DEC2BIN(SUM((INDEX(A2:D2,N(IF(1,{4;3;2;1})))<>0)*(2^(ROW(INDIRECT("1:"&COLUMNS(A2:D2)))-1)))),"0000")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[lrobbo314]

And thinking about it, the formula can be made a lot more simple.

Book1
	A	B	C	D	E	F	G	H	I
1	Data 1	Data 2	Data 3	Data 4			Result	Array Formula	Less Crazy Formula
2	Apple	Banana					1100	1100	1100
3		Orange		Lemon			0101	0101	0101
4			Grape				0010	0010	0010
5		Apple	Banana				0110	0110	0110
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=TEXT(DEC2BIN(SUM((INDEX(A2:D2,N(IF(1,{4;3;2;1})))<>0)*(2^(ROW(INDIRECT("1:"&COLUMNS(A2:D2)))-1)))),"0000")
I2:I5	I2	=DEC2BIN(SUM((A2:D2<>"")*(2^{3,2,1,0})),4)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Eric W]

Non-array version:

=DEC2BIN(SUMPRODUCT((A2:D2<>"")*2^{3,2,1,0}),4)

or for an arbitrary number of columns:

=DEC2BIN(SUMPRODUCT((A2:D2<>"")*2^(COLUMN(D2)-COLUMN(A2:D2))),COLUMNS(A2:D2))

 

[kayza]

Thank you for all the responses

Earlier, I apologize for not mentioning which version of Excel I am using. So that some functions did not work properly on my workbook
But I say many thanks for all the help.

and for mr Fluff. the UDF function you wrote works perfectly. Thank you very much

 

[Fluff]

Glad we could help & thanks for the feedback.

Don't forget to update your account details to show your version of Excel & then scroll down & click save.