Count different type of text and give me text and sum of numbers

List of people with two letter state and I want to count states and the result would be how many states and what states?

STATE
Anne NY
Nina
Joanna FL
Gabriela MO
Linda
Sigrid NY
Bella
Heida
Sandra
Eva

NY 2
FL 1
MO 1
How to get excel to give me this result?

Not at all clear ...

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

In D14 copied down:

=COUNTIF($C$3:$C$12,C14)

You may need to replace the comma with a semi-colon for your Icelandic locale.

Try

in D14

=COUNTIF($C$3:$C$12,$C14)

And in C14 copied down:

=IFERROR(INDEX($C$3:$C$12,MATCH(0,IF(ISBLANK($C$3:$C$12),"",COUNTIF(C$13:$C13,$C$3:$C$12)),0)),"")

The problem is that I do not have this list in the begining
NY
FL
MO

See post #7.

I cannot see how this would relate to a longer list elsewhere.

I think if you have not got what you want so far (and you really haven't commented on ANY of the solutions offered!), then you need to explain in more detail the wider context of what you are trying to do.

It doesn't matter. The formulae I gave you will sort it out for you. See the workbook attached.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

You may need to enter the formula as an array formula:

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Also I am wondering why you have in your formula this C$13:$C13 since it is a cell that contains nothing.

It makes the formula count how many times a state has appeared - it makes sure that the list is unique. You really don't need to worry about it that much.

Hope you can help with this

Beyond what I have just said, no, not without seeing the new workbook. It could be all sorts of things. Please attach a new version for me to look at for you.

if you don't mind to use helper cells.

E3 (copy to each row)
=IF(COUNTIF(C$2:C3,C3)=1,E2+1,E2)

G14 to G18 enter 1,2,3,4,5

H14
=IF(G14<=MAX($E$3:$E$12),INDEX($C$3:$C$12,MATCH(G14,$E$3:$E$12,0)),"")

I14
=IF($G14<=MAX($E$3:$E$12),COUNTIF($C$3:$C$12,H14),"")

Regards.

Originally Posted by Mariaoskars

Ok I know about CTRL+SHIFT+ENTER ...

Just share the new workbook and I will give you a step-by-step. I'm not going to go through all the various things that you might have got wrong when seeing the workbook will show instantly what that is.

Just desensitise it. I can cope with Icelandic ...

Copy it - cut out any worksheets that are not relevant. Desensitise anything left that is confidential (e.g. use names like Person1, Person2, etc.). Remove ANYTHING that is not necessary.

If you make an effort to help me, then I'll make an effort to help you. Sadly, I am not psychic.

Well done!!! So glad you worked it out. Thanks for the kind comment.

Seem to be some circular references on both the Jan22 and Frístund sheets which may be causing problems with getting the expected results.
That said, it may help us understand the issue if we knew the expected results for cells AF2:AF10 on the Jan22 sheet.
Let us know if you have any questions.

Please try

AF2 (follow with Ctrl-Shift-Enter) then copy down.
=IFERROR(INDEX(Y$2:Y$300,MATCH(0,COUNTIF(AF$1:AF1,Y$2:Y$300),0)),"")

Regards.

The message that I see is that the circular reference on the Jan22 sheet is in cell O146 so not column B.
When I asked for the expected results for cells AF2:AF10 on the Jan22 sheet, I was asking you to tell/show us what should be in each of those cells, not a description of how they should be filled.
Please either list in your next post, or manually show in an Excel file, what each cell in the range AF2:AF10 should display.

Paste the following array entered formula** into cell AF2:

Formula:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
After the formula is in cell AF2 is activated, drag the fill handle down to cell AF12.
Let us know if you have any questions.

As there are only #VALUE! errors in column AC I am not able to illustrate, however you may be able to use a combination of INDEX and AGGREGATE to produce a summary.
The formula would be similar to:

Formula:

Please Login or Register  to view this content.

Note that the above formula may be activated by simply pressing the Enter key.
If you need further help please clear the #VALUE! errors in column AC and attach the file to your next post.

Wouldn't a pivot table be simpler? You just need to refresh it when your data changes.

To illustrate rorya's suggestion, on the Pivot Table sheet the pivot table has Value Filters in the Nr column set to Does Not Equal zero.
Let us know if you have any questions.

For some reason Sucurri is not allowing me to post my response, so I have put it in column T of the attached file.
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.