COUNTIF Multiple Worksheets

[Bethany80]

Hi,

I have 5 worksheets that I'm trying to count names on. There is no range, I just want to count how many times anywhere in the workbook that a name appears.

 

[danzon]

how about,

=COUNTIF(Sheet1!1:1048576,"dan")+COUNTIF(Sheet2!1:1048576,"dan") and so on to count the appearances of dan

 

[Bethany80]

For some reason, this isn't working. I'm receiving the NAME? error. I have five tabs named as dates (Tuesday April 5th, Wednesday April 6th, etc.), and it is asking me for a source for 5th, 6th, etc.

Also, is there a way to copy and paste this information onto a new spreadsheet without the formulas changing?

 

[danzon]

Your problem is being caused by the spaces in your tab names....

so try it like this and it should work

=COUNTIF('Tuesday April 5th'!1:1048576,"dan")+COUNTIF('Wednesday April 6th'!1:1048576,"dan")+ etc etc

take note of the exta single quote ' surrounding the sheet names - it is required when your tab names have spaces


PS - your range will need to be smaller if working in excel 2003