Countif for horizontal arrays in a larger 2D array

[des378]

Hi,

Need to count any numbers from a 2D array if it is standalone in a horizontal row - formula needs to work for whatever column its in

Ex:

COLUMN A	COLUMN B	COLUMN C	COLUMN D
25​		94​
83​
92​	78​	32​
	56​
78​	21​
80​	31​
		64​	21​
			31​
			91​

COLUMN A OUTPUT: 1 (only one number - 83 with no other numbers on that row)

COLUMN B OUTPUT: 1 (only one number - 56 with no other numbers on that row)

COLUMN C OUTPUT: 0 (all rows contain some number in it)

COLUMN D OUTPUT: 2 (two rows - 31 and 91 with no numbers in it)


Thanks!

 

[Cubist]

Here's one option, try:

Book1
	A	B	C	D
1	25		94
2	83
3	92	78	32
4		56
5	78	21
6	80	31
7			64	21
8				31
9				91
10
11	1	1	0	2
Sheet6

Cell Formulas
Range	Formula
A11:D11	A11	=MAP(SEQUENCE(1,COLUMNS(A1:D9)),LAMBDA(m,SUM(--(BYROW(A1:D9,LAMBDA(r,(COUNTA(r)=1)*XMATCH(TRUE,r<>"")))=m))))
Dynamic array formulas.

 

[Tetra201]

Another option:

=LET(x,SIGN(A1:D9),MMULT(SEQUENCE(1,ROWS(x),,0),x*(MMULT(x,SEQUENCE(COLUMNS(x),,,0))=1)))

 

[Engberg]

You've got good answers already, here's an UDF version though just for fun. Arguments are a mandatory range for your table, and an optional cell/1 column range to know which column to look at. If you don't set the optional range the function will look at the same column that it is used in.

Function findIsolatedValues(table As Range, Optional optionalCol As Range)
On Error GoTo ErrorHandler
    If table.Columns.Count < 2 Then findIsolatedValues = "<Input table can't be single column>": Exit Function
    Dim colAsNr As Double

    If optionalCol Is Nothing Then
        colAsNr = Application.Caller.Column
    Else
        If optionalCol.Columns.Count > 1 Then findIsolatedValues = "<Input optionalCol must have 1 column>": Exit Function
        colAsNr = optionalCol.Column
    End If

    cLVC = 0
    For Each r In table.Rows
        If Cells(r.Row, colAsNr).Value <> "" Then
            rVC = 0
            For Each c In r.Columns
                If c.Value <> "" Then rVC = rVC + 1
            Next
            If rVC = 1 Then cLVC = cLVC + 1
        End If
    Next
    findIsolatedValues = cLVC
    Exit Function
ErrorHandler:
findIsolatedValues = "<Error>"
End Function

 

[des378]

@Cubist thanks for the solution - it works great for what I need. @Engberg love the VBA code as well but a little too complicated for what I'm trying to achieve - thanks anyways!

 

[Cubist]

@Cubist thanks for the solution - it works great for what I need. @Engberg love the VBA code as well but a little too complicated for what I'm trying to achieve - thanks anyways!

You're welcome but did @Tetra201 solution work for you? It's shorter.