The sheet names are 2007, 2006, 2005, 2004, 2003, 2002, 2001, 2000, 1999,
1998, 1997, 1996, and lastly, Combined. In the combined sheet is where I
would like to be able to look at the information that is in all of the other
sheets. In the other sheets under column F in Row 1 is state. Starting in Row
2 to Row 50 is the abbreviated state such as CO, NE and WY. I would like to
be able to show in the Combined sheet how many there are in CO, NE, WY, etc.
Im still a little unsure of your formula and how to enter it.

Thank you,

Peter


"Teethless mama" wrote:

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