Counting the number of specific words in multiple sheets

[Spartak19]

I've got a spreadsheet with different names in a column, im trying to count the number of times a name occurs. So far I have worked out this formula - =SUMPRODUCT(LEN(D:D)-LEN(SUBSTITUTE(UPPER(D:D),UPPER(D3),"")))/LEN(D3) The name im looking for is in D3 and im looking to see how many times it occurs in the same column, with the column being D.
The bit im now stuck on is that its a weekly thing and ive got 4 different worksheets set up for each week. How do I count the number of occurrences in the 4 different sheets.
(The sheets are all identically set up, just different data in the columns.)
Thanks in advance

 

[Trebor76]

Hi Spartak19,

As 3D formulas do not seem to work, here's one way to count the names in Col. D for Sheets1, Sheets2, Sheets3 and Sheets4 (change to suit):

=COUNTIF(Sheet1!D:D,"*"&Sheet1!D3&"*")+COUNTIF(Sheet2!D:D,"*"&Sheet2!D3&"*")+COUNTIF(Sheet3!D:D,"*"&Sheet3!D3&"*")+COUNTIF(Sheet4!D:D,"*"&Sheet4!D3&"*")

Note the entry in cell D3 will be added to the total.

Thanks,

Robert