CODEWITHSUNDEEP
vbaexcelexcel-formula
cplus
cplus

Reputation: 1115

COUNTIFS with floating criteria matching the next value in a column

I have the following example data with four columns Class, ID, Type and Code: 

Class   ID  Type    Code
Class1  ID1         Code1
Class1  ID1 Type1   
Class1  ID1 Type2   
Class1  ID1     
Class1  ID1         Code1
Class1  ID1     
Class1  ID1 Type1   
Class1  ID1         Code1
Class1  ID1 Type2   
Class1  ID1     
Class1  ID1         Code1
Class1  ID1     
Class1  ID1 Type2   
Class1  ID1 Type1   
Class1  ID1         Code1
Class1  ID1 Type2   
Class1  ID1 Type2   
Class1  ID1 Type2

I have the following formula:

=COUNTIFS($A:$A;"Class1";$B:$B;"ID1";$D:$D;"code1")

BUT I want to achieve a countifs formula with an additional condition:
count only those code1s that have type1 as the next occurence in column TYPE
So I will have: by maintaining the above conditions in the formula and adding the additional condition: the count of code1 with type1 as the next occurrence will be 2. And similarly, the count of code1 with type2 as the next occurrence in TYPE column will be 3.

Edit: Actually I know how to find my nth match using:

SMALL(IF($C$1:$C$51="type1";ROW($C$1:$C$51);"");1)

but I don't know how to merge these two formulas.

Acually, I can already locate my code1 and then in relation to its cell reference locate my type1 in the previous column one row below, but it is not always one row below, sometimes it is two or three or more.

=OFFSET(INDEX($D:$D;MATCH(1;(D:D="code1")*(A:A="class1");0);1);1;-1;1;1)


I feel that it has something to do with sumproduct and offset or index/match, but I cannot find my way through it.


**P.S. ** I also tried the following, but still no result:

=MMULT(($A:$A="Class1")*($B:$B="ID1")*($D:$D="code1");N(OFFSET(INDEX($D:$D;MATCH(1;(D:D="code1")*(A:A="class1");0);1);1;-1;1;1)="type1"))

UPDATE

Since it seems a bit of problem achieving this, I am adding this update to see how we can achieve this using a UDF?

Upvotes: 2

Views: 1008

Answers (2)

user4039065
user4039065

Reputation:

Overly complex array formulas chew calculation resources up quickly and looking for the first non-blank entry in an offsetting range comes into this category. Sometimes it is simply easier to introduce a helper column with a portion of the information necessary to arrive at an expedient result.

In an unused column to the right use this standard formula in row 2.

=IF(LEN($D2), INDEX($C2:$C$999, MATCH("*", $C2:$C$999, 0)), "")

Edit the 999 ending row if you need more rows. It can be left alone if you have less. As a standard formula this is entered normally. Fill down as necessary to catch all of the returns.

      Helper Columns for COUNT

The MATCH("*", $C2:$C$999, 0) could also be MATCH("Type*", $C2:$C$999, 0) is you need more specific lookups.

Now you can use a conventional (and efficient) COUNTIFS function to achieve your results.

Addendum: UDF alternative

    UDF for condition floating range COUNT

The code for the UDF is:

Function udf_CountIf_Next_Item(rR1 As Range, sS1 As String, _
    rR2 As Range, sS2 As String, rR3 As Range, sS3 As String, _
    rR4 As Range, sS4 As String, Optional sS5 As String = "")

    Dim rw As Long, rws As Long, n As Long
    With Intersect(rR1.Parent.UsedRange, rR1)
        rws = .Rows.Count
    End With
    For rw = 1 To rws
        If LCase(rR1(rw).Value2) = LCase(sS1) Then
            If LCase(rR2(rw).Value2) = LCase(sS2) Then
                If LCase(rR3(rw).Value2) = LCase(sS3) Then
                    If Not IsError(Application.Match(sS5 & Chr(42), rR4(rw).Resize(999, 1), 0)) Then
                        If LCase(Application.Index(rR4(rw).Resize(999, 1), _
                          Application.Match(sS5 & Chr(42), rR4(rw).Resize(999, 1), 0))) = LCase(sS4) Then
                            n = n + 1
                        End If
                    End If
                End If
            End If
        End If
    Next rw

    udf_CountIf_Next_Item = n

End Function

syntax:
 
udf_CountIf_Next_Item(<1st column>, <1st criteria>, <2nd column>, <2nd criteria>, <3rd column>, <3rd criteria>, <special floating column>, <4th criteria>, [optional]<partial floating criteria>)

The formula in J7 of the above sample image is:

=udf_CountIf_Next_Item($A:$A, $F7,$B:$B, $G7,$D:$D, $I7,$C:$C, $H7, "Type")

Full column references shouldn't be a problem as the UDF truncates them to the boundaries of the worksheet's UsedRange property. Partial column references like A2:A99 can still be used.

Upvotes: 2

Per76
Per76

Reputation: 184

I'm not a pro at Excel but I think you can solve this with INDEX().

Or if you go into Range.FindNext Method

https://msdn.microsoft.com/en-us/library/office/ff196143.aspx

Hope some if this will help you.

EDIT: sorry. I was wrong. But learned a lot.

Upvotes: -1

Related Questions