Match columns with COUNTIF by rows

Question

I am trying to create a custom function to evaluate some data. The data consists of a few samples (in columns) taken from a living organism and the bacteria + their magnitude (in rows). I know which bacteria are unique (only appear in 1 sample) but I want to know how many unique bacteria are in each sample.

To know which bacteria are unique I use =COUNTIF(A:A,">"&0) on each row and if returns a 1 then it is unique.

Ideally I was thinking of something along the lines of:

Function Custom(sampleRange, occurringBacteria) As Integer

Dim bacteriaUniqueToSample as Integer: bacteriaUniqueToSample = 0

For Each sampleRange And occurringBacteria
 If sampleRange > 0 And occurringBacteria = 1
    Then bacteriaUniqueToSample = bacteriaUniqueToSample + 1
Next
Custom = bacteriaUniqueToSample
End Function

Of course, that is not possible.

Example:

See Justin's answer here: http://stackoverflow.com/questions/1676068/count-unique-values-in-excel — Tim Williams, Oct 16 '15 at 23:44
@TimWilliams Screenshot added. I looked at that answer and it is not what I am looking for. — Alexander, Oct 17 '15 at 01:55

score 1 · Accepted Answer · answered Oct 17 '15 at 02:29

1

Enter this in cell B7 and copy across till D7

=COUNTIFS(B$2:B$5,">"&0,$E$2:$E$5,"Unique")

answered Oct 17 '15 at 02:29

EEM

6,601
2
18
33

How could I make that work like this, `=COUNTIFS(B$2:B$5,">"&0,B$2:D$2,COUNTIF(B$2:D$2,">"&0) =1)`? **That line doesn't quite work.** – Alexander Oct 17 '15 at 03:22
@Alexander -Do you HAVE to break the use of E2:E5 as a 'helper' column? If you are already calculating *Unique* vs *Not Unique* then this seems like an ideal solution. – Oct 17 '15 at 03:30
@Jeeped This already works. I was just curious as if it would work without the extra column. – Alexander Oct 17 '15 at 03:34

score 1 · Answer 2 · answered Oct 17 '15 at 03:55

A User Defined Function (aka UDF) could be written for this. While your sample layout has a correlation between the cell with the function and the cell holding the Sample1, Sample2, etc labels, to be more universal you will need to provide a wider worksheet relationship.

Function uniq_bacts_by_sampl(rSample As Range, rOccurringBacteria As Range)
    Dim cnt As Long, rw As Long, rng As Range

    For rw = 1 To rOccurringBacteria.Rows.Count
        Set rng = Application.Index(rOccurringBacteria, rw, 0)
        If Application.CountIf(rng, ">" & 0) = 1 And _
          CBool(Cells(rng.Row, rSample.Column).Value2) Then
            cnt = cnt + 1
        End If
        Set rng = Nothing
    Next rw

    uniq_bacts_by_sampl = cnt
End Function

The UDF is put to use just as any other native worksheet function.

Syntax:
=uniq_bacts_by_sampl(<cell with label criteria>, <data range>)

The formula in the above sample image's B7 is,

=uniq_bacts_by_sampl(B$1, $B2:$D5)

Fill right as necessary.

Match columns with COUNTIF by rows

2 Answers2