Clever Geek Handbook

Counting error values ​​like any other value

I have a list of data as shown below. I am trying to count how many occurrences of each unique value.

But some of these values ​​represent error values, for example #N/A

. Is there a formula that can count both normal values ​​and error values?

This one (entered as an array formula) can count a

other regular values ​​as well:

=SUM(--IFERROR($A$2:$A$14=C2,FALSE))

But if I copy it over, obviously it doesn't work for error totals as it ignores errors with IFERROR

.

I know I can count #N/A

with this:

=SUM(--ISNA($A$2:$A$14))

but this is a completely different formula; I can't just drag it to the column.

I'm looking for a formula that can accommodate both without "modifying the source".

COUNTIF

will actually count them correctly, but I don't think I can use it as it only means part of the formula of a larger array.

enter image description here

+3


source to share


3 answers


If you can't use COUNTIF

, try the following formula inD2 



{=SUM(IFERROR($A$2:$A$14=C2,FALSE)+IFERROR(ERROR.TYPE($A$2:$A$14)=ERROR.TYPE(C2),FALSE))}
+2


source


=SUM(--IF(ISERROR(C2),IF(ISNA(C2),COUNTIF($A$2:$A$14,"#N/A"),COUNTIF($A$2:$A$14,"#VALUE!")),IFERROR($A$2:$A$14=C2,FALSE)))

It looks like this does what you want and works for me in an array formula.



enter image description here

+1


source


Try this little UDF ():

Public Function Kount(rng As Range, what As Variant) As Long
    Dim r As Range, st1 As String, st2 As String
    st1 = CStr(what)
    For Each r In rng
        st2 = r.Text
        If st1 = st2 Then
            Kount = Kount + 1
        End If
    Next r
End Function

For example:

enter image description here

In this example, the C4 and C5 values ​​are text values.

0


source






More articles:


All Articles