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]

So, it is supposed to be counting the values on the other 5 tabs to give you the total counts, right?
How are the values (that are being counted) on the other tabs being updated?
By formulas? Links? Hard-coded?

Note that if you are just trying to count the values across all sheets, except for your summary sheet, you may be able to do it without VBA, as shown here:

Redirecting

 

[joshbrfc]

So, it is supposed to be counting the values on the other 5 tabs to give you the total counts, right?
How are the values (that are being counted) on the other tabs being updated?
By formulas? Links? Hard-coded?

Note that if you are just trying to count the values across all sheets, except for your summary sheet, you may be able to do it without VBA, as shown here:

Redirecting

There are lots of tabs.

It matches the employee name on the respective tab to that employee name in the master data tab and populates that way automatically on the master data tab. Each individual tab is updated manually.

 

[Joe4]

OK, let's take a step back and see if we can come at this from a different angle.
Is there any way we can rename the sheets, so each sheet name is EXACTLY the same as the Employee Name?
Then we can write a formula without VBA that will exactly at just the sheet it needs to look at to get the data.

 

[joshbrfc]

OK, let's take a step back and see if we can come at this from a different angle.
Is there any way we can rename the sheets, so each sheet name is EXACTLY the same as the Employee Name?
Then we can write a formula without VBA that will exactly at just the sheet it needs to look at to get the data.

You mean just instead of initials on the tab, just put the actual persons name?

 

[Joe4]

Yes, if the name of the Sheet exactly matches the name field in column A of the summary, I think we may be able to do it easily without any VBA necessary.

 

[joshbrfc]

Yes, if the name of the Sheet exactly matches the name field in column A of the summary, I think we may be able to do it easily without any VBA necessary.

Yes okay, i will change them now. This may help me in the next step, and that's gathering information from all the sheets to add in common error reasons.

 

[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]

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"))

You sir.... are a legend! It's working great.

Thanks so much. I messed with this for ages, but just couldn't get there.

For some reason when the cell is empty it adds it as one.. but that's not an issue.... just remove the empty cell haha!

I'm now going to to expand on this and try to get trends from each tab for the error reasons each person is making and the most common one etc. EG a word typed in in B20:B1000 in each tab.... calculate the entries on all of the tabs for said word.

 

[Joe4]

For some reason when the cell is empty it adds it as one.. but that's not an issue.... just remove the empty cell haha!

You could modify the formula like this:

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

 

[joshbrfc]

G

You could modify the formula like this:

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

Great thanks.

Before i over complicate it, is there a way to calulate the following from all tabs in a workbook?

This is on the master data tab:

To pull from quantity of entries with the reason in column J(master data) that match column B in every single tab