CODEWITHSUNDEEP
excelif-statement
Eaj0894
Eaj0894

Reputation: 1

IF Statement in Excel using multiple worksheets

I'm trying to make an IF statement that adds 1 for every set of cells that matches a certain criteria:

=SUM(IF(AND(Sheet1!A1=B2, Sheet1!C2=B3),1,0))

However, whenever I make a new sheet I want the B2 and B3 of that sheet to be included in the relationship. Is there a way to do this while also not including B2 or B3 in Sheet1?

Upvotes: 0

Views: 122

Answers (1)

Aditya Pansare
Aditya Pansare

Reputation: 1132

You can create a function. Open VBA Editor, Insert module and paste below mentioned code.

Function FINDMATCH() As Long
    Dim Sheet1, sht As Worksheet
    Dim counter As Long
    Set Sheet1 = ThisWorkbook.Sheets("Sheet1")

    counter = 0
    For Each sht In ThisWorkbook.Worksheets
        If sht.Name <> "Sheet1" Then
            If Sheet1.Range("A1").Value = sht.Range("B2").Value And Sheet1.Range("C2").Value = sht.Range("B3").Value Then
                counter = counter + 1
            End If
        End If
    Next

    FINDMATCH = counter
End Function

Add values to be checked at A1 and C2 of Sheet1 and at B2 & B3 in every other sheet.

Write function =FINDMATCH() in any cell and you will get the result.

Upvotes: 1

Related Questions