Counting cells that match criteria in a list

[obrien979]

OK I am not sure how to word the title, so I am sorry about that. I am going to attempt to explain what I am trying to do here. First off I have a table that tracks the soldiers I have at a particular location. The Table shows their Name and Rank in one cell, male or female in next cell, and what shift they are on in the last cell. I would like to be able to sum up the cells that contain criteria that I have listed in a seperate list on a seperate sheet. For example, my list of Criteria are the different ranks of soldiers we have. I would like to search each cell, compare the data to the list of ranks, and if that cell contains any of the data on that Criteria list then count it. I have to be able to do this across multiple ranges and then as I add and subject personnel. Having a little difficulty adding an example of what I need.

To try and sum it up, I need compare a cell to a range of ranks, if the cell contains that rank, count it..if not dont count it. The cell will look something like SPC Smith.
Please let me know if you need any further info to help.

Thanks a million!

Here is the list of Ranks:

RANKS
NCOS Soldiers Officers
CSM SPC LTC
SGM PFC MAJ
1SG PV2 CPT
MSG PVT 1LT
SFC 2LT
SSG WO1
SGT CW2
CPL CW2
CW3


and I want to compare those ranks to the below cells to see if they contain any of those ranks and then count them under each of the above categories (NCO, SOLDIER, OFFICER)

SGT Tippenhauer
SGT Echevarria
SPC Harris
PFC Barker
PFC McGrew
SGT Iaulualo
WO1 Brown
CW2 Whitley
1LT Palsgrove
CPT Simons
SGT Jayme
PFC Sadler
PFC Geniesse
PFC Hampton
SPC Burkhart
SSG Olsen
CW2 Sparger

[p24leclerc]

You just need a Countif function like this:
Assuming the soldier table have the rank column in column A.
Assuming you want the results in Column H (Ranks list) and I (Counts).
You place this formula in cell I2 and copy it down your ranks' list.

Please Login or Register  to view this content.

[obrien979]

That is the unfortunte thing the Rank and Name are in the same cell. I have strict guidance as to not mess with the design of the form, just figure all the automatic calculations out.

[p24leclerc]

Here is a formula that should work:

Please Login or Register  to view this content.

Hope this helps

[obrien979]

Works perfectly thank you oh so much, now I am off to figure how to sort those same cells by rank from highest rank to lowest rank.

[p24leclerc]

Can you add a formula next to this table so we can set a sorting value based on ranks?

Assuming your ranks list is properly ordered from highest to lowest rank, you can add this formula in cells next to your name list.

Please Login or Register  to view this content.

This formula only works if there is a space between the rank and the name of the soldier. And the rank must be the first information in the cells.
You can change the range $H$2:$H$5 in the formula to suit your complete ranks list.
The only thing left to do is to sort your list by this new column.

[obrien979]

So if I was to put the ranks like so will that function work to assign them all a value. I can always hide that column so no one tries to screw with it.

LTC
MAJ
CPT
1LT
2LT
CW3
CW2
WO1
CSM
SGM
1SG
MSG
SFC
SSG
SGT
CPL
SPC
PFC
PV2
PVT

Yes there is a space between the Rank and Name. So will it assign all the same ranks the same value so that I can sort them all approriately?

[p24leclerc]

Absolutely. Try it.

[obrien979]

will this formula work if I have the list of ranks ordered like above on a seperate sheet, will the values still be assigned and the sorting work?

[p24leclerc]

Yes, the only difference will be the reference to the sheet where you have your list of ranks like in :

Please Login or Register  to view this content.

You replace Sheet2 by your sheet name. CAUTION: if your sheet name has spaces in it, you must enclose your name within quotes like this:

Please Login or Register  to view this content.

[obrien979]

Screenshot.jpg

Not sure If I did this right. The ranks are listed on Calculation Sheet in the range shown in bar,Listed from highest rank to lowest. Any guidance?

[p24leclerc]

Hard to tell from picture like this.
Reference to your rank list seems OK.
I saw that your soldier data start in column B.
I also saw that you want to put the formula in column A.
No problem with that as long as you adjust the formula properly. So, in the formula, you can see reference to cell A2 which was where my soldier list was starting. As yours starts at cell B5, you must change every A2 by B5 in that formula. Then copying it downward, it will adjust to B6 then B7, etc.

The other thing I saw on you picture is that you have 4 different columns of soldier names. Column B, E, H and K. Do you want all of those columns to be sorted?

[obrien979]

Yes I would like to sort all columns that contain SOldier names. Also once I paste the formula into the first cell, make the adjust as needed to get a value; then I drag the formula down..the range of lookup (list of ranks) increases by one. So when it is populated into the cell below it goes from O9:O30 to O10:O31..did i do something wrong?

[p24leclerc]

You have to put $ in front and after the column letters in your range like in $O$9:$O&30

[obrien979]

One last question and I think it will close out this project, is there a way to set up some sort of Auto Sort that I can sort by the rank automatically once new names are placed into the cells?

[p24leclerc]

The only sort I know is part of the Data menu. Once you set up the sort parameters, it is easy to sort again your data. Usually, it is self-adjusting to your cells content. So it should detect any new names and include them in the sort.
Otherwise, there would be macros that could help you.