Countif match

[joshbrfc]

Hi all,

I want to count the quantity of entries in a row on a specific tab in a workbook based on the match of someones name.

So this is the master data tab.

Then i have separate tabs.

And i want it to match with the name cell and the quantity of data entries.

Hope that makes sense.

Would Lookup be better?

So it updates automatically when another data entry is added.

Any help greatly appreciated. It's part of trying to improve right first time Want a tracker to focus on who needs training more and quick overview.

 

[Joe4]

Excellent!

So then if you have an entry in cell A2 on your Summary sheet, and you want to count the number of entries in cells B2:B1000 on the sheet with the same name as in cell A2, this is the formula you would put in cell B2:

=COUNTA(INDIRECT("'"&A2&"'!B20:B1000"))

 

[joshbrfc]

Anyone able to offer anything on this? At all?

 

[Shloime]

Would there be a separate sheet for each employee?

 

[joshbrfc]

Would there be a separate sheet for each employee?

All on the same workbook, but each employee has separate tabs, yes. I want a 'master data' tab where it's auto populating based off what is entered on each individual tab.

 

[Shloime]

All on the same workbook, but each employee has separate tabs, yes. I want a 'master data' tab where it's auto populating based off what is entered on each individual tab.

What do you want to count ? is it cell M8 in the pic. above?

 

[joshbrfc]

What do you want to count ? is it cell M8 in the pic. above?

It is the quantity of entries in the 'marker' column.

So it matches with the employee's name then pulls the quantity of entries in that column

 

[Shloime]

It is the quantity of entries in the 'marker' column.

View attachment 106911

So it matches with the employee's name then pulls the quantity of entries in that column

I don't know of any formula to match data across worksheets, but it can be done with a macro
try a User defined function like this:

Function CountEntries(Employee_Name As String)
Dim WS As Worksheet
Dim Entries As Long
For Each WS In ThisWorkbook.Worksheets
If Not WS.Name = ActiveSheet.Name Then
If WS.Range("B3") = "Employee" Then
If WS.Range("F3") = Employee_Name Then
CountEntries = Application.WorksheetFunction.CountA(WS.Range(WS.Range("B20"), WS.Range("b20").End(xlDown)))
Exit Function
End If
End If
End If
Next
End Function

click alt+f11 or go to developer tab and click on visual basic
in the visual basic window click on insert then choose moudle
copy the function above and paste in the moudle
then in the master data tab
next to the name in your example you have the first name in cell a2 and you want the number in cell B2
so in cell b2 type =CountEntries(a2)

 

[joshbrfc]

I don't know of any formula to match data across worksheets, but it can be done with a macro
try a User defined function like this:

Function CountEntries(Employee_Name As String)
Dim WS As Worksheet
Dim Entries As Long
For Each WS In ThisWorkbook.Worksheets
If Not WS.Name = ActiveSheet.Name Then
If WS.Range("B3") = "Employee" Then
If WS.Range("F3") = Employee_Name Then
CountEntries = Application.WorksheetFunction.CountA(WS.Range(WS.Range("B20"), WS.Range("b20").End(xlDown)))
Exit Function
End If
End If
End If
Next
End Function

click alt+f11 or go to developer tab and click on visual basic
in the visual basic window click on insert then choose moudle
copy the function above and paste in the moudle
then in the master data tab
next to the name in your example you have the first name in cell a2 and you want the number in cell B2
so in cell b2 type =CountEntries(a2)

I did try this before but got it wrong, so thankyou ever so much for your replies and for your effort! You sir, are a legend

So i've done it now

Based on

and

Am i being really slow but it's saying invalid name

 

[Shloime]

I did try this before but got it wrong, so thankyou ever so much for your replies and for your effort! You sir, are a legend

So i've done it now

View attachment 106912

Based on

View attachment 106913

and
View attachment 106915

Am i being really slow but it's saying invalid

Check if the ranges are correct do you have in each sheet the word employee in cell B3 and the employee's name in cell F3?