Let's say I have a spreadsheet and the first sheet is a list of clients and their information, with the state they live in all in the same column. If I wanted to create a second page that would search that column for how many clients I have in each state, what would be the formula?

Essentially I want to search the column on sheet one and populate 50 fields for each state on the second sheet to know how many clients we have in each state. Thanks!

You could do one of two things. On second sheet, you could list all states in column 1, and use COUNTIF in the second column. I'm not at my pc, but the formula is pretty straightforward. Another option is to insert a pivot table in sheet 2 using the sheet 1 data as your source.

countif is what you need.

If
the clients' states are in sheet 1 column B
your summary by state is in sheet 2 with the states listed in column A
then
in sheet 2 cell B1 type
=countif('sheet 1'!B:B,A1)
and copy it down

That's the first option CJ said. Pivot table will work too, but you'll likely have to remember to refresh your data.

Does countif look into the cell to find the state or does he need to parse out the state into its own column sparate from the other information?

For the formula above the State will have to be parsed.

However, if not parsed, this should work.

=COUNTIF('sheet 1'!B:B,"*"&A1&"*")

This is basically searching for the State value (A1) and using wildcard characters to ignore other text.

This could provide erroneous results however if sheet 1 B contained the word "ALL" when looking for AL (Alabama).

There are some other options using FIND, SEARCH, LEN (text functions) that can be used to parse within the COUNTIF formula instead of parsing the data and creating a separate State column in sheet 1.