Counting the number of times an employee number appears in multiple sheets.

[DHaste]

Hi Guys,

I always turn here when suck and so far you have never let me down.. so no pressure.

I have attached the majority of the sheets that I'm working on and what I'm trying to do is thus:

There are tabs with the dates on - 110912, 120912, 130912 and these will continue for the next two weeks as I get the data in. In Column F on every sheet it shows the employee number; each line with a persons employee number represents a bonus to them as they are paid a fixed amount for each transaction.

On the 'Staff' tab there is a list of each employee and their numbers and the days across the top. All I want to do is put a formula in the 'Staff' tab that will show me how many times an employee number appears in each different date tab and put the data in the correct column. (As an example employee 986 has done 21 transaction across the three days and I have manually shown this in the 'Staff' tab in Red)

I can then very easily at the end of the two weeks (maybe longer) very easily see that employee 986, has appeared 358 times and therefore gets a bonus of 358 x £x.

Any anticipated help would be much appreciated.

Thanks - Darren£1 OVER £250 Incentive - Excel Forum Help.xlsx

[NBVC]

Try, in D2:

=IFERROR(COUNTIF(INDIRECT("'"&D$1&"'!F:F"),$A2),"")

copied across and down.

[DHaste]

Many thanks for that; it works a treat!

Would you mind explaining to me how it works though??

Kind Regards

Darren

[NBVC]

The INDIRECT function indirectly references the sheet names found in Row1 and looks at column F in that sheet, where the COUNTIF takes over and counts matches to the value in A2. The IFERROR returns a blank if any errors occur. IF you do get blanks though, it is not because of mismatches (countif would return a 0 result), it would be likely because sheetnames are not exactly matched... so take a look at that.