Using countifs for entire wokrbook

[TsoogThoj]

Hi. I am new to excel and I have searched the web for about a week now and couldn't find any solution to what I want. I found out that I can't use countifs across multiple worksheets without using VBA so can someone would help me to create a script for my problem? I found people making a list of all the sheets but it wouldnt work in my situation because multiple of sheets are added daily. I found countif in VBA only, no countifs.

Here is what I want to do.
I want to use countifs with two different criteria (for now).
I want to use it across the entire workbook.
I want to it look in new sheets too as it gets added to workbook.
Each sheets will be name differently as it gets added in.
This is what I want to do but across the entire workbook.
=COUNTIFS(Sheet1!$B$7:$B$25,Main!A3,Sheet1!$C$7:$C$25,Main!B3)

Thank you in advance

 

[TsoogThoj]

I couldn't find the edit button to add more information so here is additional information. I hope this help.

I want to find how many times someone, first and last name, occurs in each sheets.
The "first name" would be in one cell and the "last name" would be in the adjacent cell. Example: A3 = Tom, A4 = Cruise
Would require two criteria because there are people with the same first name or the same last name.
The first sheet would be the "main" sheet that keeps track of the occurrence.
It would be nice if it would automatically count the occurrence as new sheets are added.

Thanks again

 

[TsoogThoj]

Asked on another forum and someone gave me the answer so just want to share it here so it can help the community here too. Credit goes to Andrea Killer. Link and answer is posted below.
https://answers.microsoft.com/en-us...4e47-a3b3-42fc88606817?rtAction=1495984989120

[FONT=&quot]The code below must be placed in a regular module.[/FONT]

[FONT=&quot]Call the UDF from the Main sheet like this:[/FONT]
[FONT=&quot] =CountIfsAcross("*",Sheet1!$B$7:$B$25,Main!A3,Sheet1!$C$7:$C$25,Main!B3)[/FONT]

[FONT=&quot]Andreas.[/FONT]


[FONT=&quot]Function CountIfsAcross(ByVal Include As String, _[/FONT]
[FONT=&quot] ByVal Where1 As Range, ByVal Criteria1 As Variant, _[/FONT]
[FONT=&quot] Optional Where2 As Variant, Optional Criteria2 As Variant, _[/FONT]
[FONT=&quot] Optional Where3 As Variant, Optional Criteria3 As Variant, _[/FONT]
[FONT=&quot] Optional Where4 As Variant, Optional Criteria4 As Variant, _[/FONT]
[FONT=&quot] Optional Where5 As Variant, Optional Criteria5 As Variant) As Variant[/FONT]
[FONT=&quot] 'Same as COUNTIFS but across all sheets[/FONT]
[FONT=&quot] ' Include is a search mask to match the sheet names, support wildcards (see LIKE operator)[/FONT]
[FONT=&quot] ' Excludes the caller sheet automatically[/FONT]
[FONT=&quot] ' Example:[/FONT]
[FONT=&quot] ' =CountIfsAcross("*",Sheet2!A1:A10,"x",Sheet2!B1:B10,"y")[/FONT]
[FONT=&quot] ' Note:[/FONT]
[FONT=&quot] ' This function is volatile and is called on every calculation in the file[/FONT]
[FONT=&quot] ' But as not all cells are referred on the call the results may not always correct![/FONT]
[FONT=&quot] ' You have to recalculate the file manually, see[/FONT]
[FONT=&quot] ' https://msdn.microsoft.com/de-de/library/office/bb687891.aspx[/FONT]
[FONT=&quot] Dim OurWs As Worksheet, Ws As Worksheet[/FONT]
[FONT=&quot] 'Include all if no search mask is given[/FONT]
[FONT=&quot] If Include = "" Then Include = "*"[/FONT]
[FONT=&quot] With Application[/FONT]
[FONT=&quot] 'Get the sheet we are called form[/FONT]
[FONT=&quot] Set OurWs = .Caller.Parent[/FONT]
[FONT=&quot] 'This function must be called on every calculation elsewhere![/FONT]
[FONT=&quot] .Volatile[/FONT]
[FONT=&quot] End With[/FONT]
[FONT=&quot] 'Visit each sheet[/FONT]
[FONT=&quot] For Each Ws In Worksheets[/FONT]
[FONT=&quot] With Ws[/FONT]
[FONT=&quot] 'Exclude our sheet always[/FONT]
[FONT=&quot] If (.Name Like Include) And (.Name <> OurWs.Name) Then[/FONT]
[FONT=&quot] 'Call COUTIFS based on the given arguments[/FONT]
[FONT=&quot] If Not IsMissing(Where5) Then[/FONT]
[FONT=&quot] CountIfsAcross = CountIfsAcross + _[/FONT]
[FONT=&quot] WorksheetFunction.CountIfs( _[/FONT]
[FONT=&quot] .Range(Where1.Address(0, 0)), Criteria1, _[/FONT]
[FONT=&quot] .Range(Where2.Address(0, 0)), Criteria2, _[/FONT]
[FONT=&quot] .Range(Where3.Address(0, 0)), Criteria3, _[/FONT]
[FONT=&quot] .Range(Where4.Address(0, 0)), Criteria4, _[/FONT]
[FONT=&quot] .Range(Where5.Address(0, 0)), Criteria5)[/FONT]
[FONT=&quot] ElseIf Not IsMissing(Where4) Then[/FONT]
[FONT=&quot] CountIfsAcross = CountIfsAcross + _[/FONT]
[FONT=&quot] WorksheetFunction.CountIfs( _[/FONT]
[FONT=&quot] .Range(Where1.Address(0, 0)), Criteria1, _[/FONT]
[FONT=&quot] .Range(Where2.Address(0, 0)), Criteria2, _[/FONT]
[FONT=&quot] .Range(Where3.Address(0, 0)), Criteria3, _[/FONT]
[FONT=&quot] .Range(Where4.Address(0, 0)), Criteria4)[/FONT]
[FONT=&quot] ElseIf Not IsMissing(Where3) Then[/FONT]
[FONT=&quot] CountIfsAcross = CountIfsAcross + _[/FONT]
[FONT=&quot] WorksheetFunction.CountIfs( _[/FONT]
[FONT=&quot] .Range(Where1.Address(0, 0)), Criteria1, _[/FONT]
[FONT=&quot] .Range(Where2.Address(0, 0)), Criteria2, _[/FONT]
[FONT=&quot] .Range(Where3.Address(0, 0)), Criteria3)[/FONT]
[FONT=&quot] ElseIf Not IsMissing(Where2) Then[/FONT]
[FONT=&quot] CountIfsAcross = CountIfsAcross + _[/FONT]
[FONT=&quot] WorksheetFunction.CountIfs( _[/FONT]
[FONT=&quot] .Range(Where1.Address(0, 0)), Criteria1, _[/FONT]
[FONT=&quot] .Range(Where2.Address(0, 0)), Criteria2)[/FONT]
[FONT=&quot] Else[/FONT]
[FONT=&quot] CountIfsAcross = CountIfsAcross + _[/FONT]
[FONT=&quot] WorksheetFunction.CountIfs( _[/FONT]
[FONT=&quot] .Range(Where1.Address(0, 0)), Criteria1)[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot] End With[/FONT]
[FONT=&quot] Next[/FONT]
[FONT=&quot]End Function[/FONT]