using countif function for multiple worksheets

[maacmaac]

I am trying to use countif to count the number of times a unique items occurs in multiple worksheets.

For example, I want to count number of times "ITEM1" occurs in row 1 of sheets1, sheets2, sheets3, sheet4, etc. It may look like this:
Sheet1 = 4 entries
Sheet2 = 22 entries
Sheet3 = 5 entries
Sheet4 = 10 entries

So the entire count would be 41 total. Thanks.

[NBVC]

If your sheets are truly named in such a pattern as Sheet1, Sheet2, Sheet3, etc.. then try:

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:4"))&"'!A1:Z1"),"Item1"))

where the 1:4 represents the sheet number range.. and A1:Z1 is the range in row 1 you want to count within

or if your sheets are named without a numeric pattern...then list the sheet names in a column somewhere in your summary sheet and name that range "TabNames" without quotes.. then apply formula like:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&TabNames&"'!A1:Z1"),"Item1"))

Where A1:Z1 is as above.

[maacmaac]

Thank you for your comments. Both methods work.

[hooshea]

How do you create the range?

[NBVC]

You should really be starting your own thread on this.. but if you just mean how to create the TabNames..

Then, enter the sheetnames in a vertical range of cells, select that range and go to Insert|Name|Define... enter TabNames in the Names in this workbook field.. the range in the Refers to field should be your selected range.

[hooshea]

Sorry about that. Thanks, I will next time.