Create a list of all sheets and use name define like MySheets. Note: you can
not use First and Last Sheets.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!F2:F5 0"),"CO"))


"Peter" wrote:

I have 13 sheets names 1996 through 2007. The last sheet is called combined
which is where I would like to display the data from the other worksheet.
Column F on all the worksheets is called State which includes data from a
particular state. Data could be CO, NE, WY, etc. The range is from row 2 to
row 50. Im trying to create a formula to be able to count how many different
states there are in all of the worksheets. The formula I came up with is
=COUNTIF('2007:1996'!F2:F50,"CO"). I get the error #VALUE! In the cell.

Can anyone help with the formula?

Peter