I have the names of 250 people on my list for a club function. Next to each one is a single character, C or M (for Chicken or Meat, obviously). Now it is crunch time, and I have to count how many Cs and how many Ms and indicate this at the bottom of the spreadsheet. Obviously a simple problem for the trained Excel users. How can I accomplish the feat, as a novice. Any suggestions greatly appreciated, as long as they do not contain slurs on my inability to perform what must be a trivial chore for the experienced user. Thank you in advance.

Take a look at this attachment and see if it helps. Two options: A Pivot Table or the Countif formula.

Attached Files

Book1.xlsx (20.2 KB, 10 views)

Thank you very much for the solution. I do have a couple of questions though:


Do you mean use a solution using COUNTIF, or a solution using a Pivot Table, i.e. one or the other? I can certainly live with that after I study each solution so I learn from it rather than parroting it.


I know I mentioned that it was a final sheet, and no more changes would be made, but what happens if I add another user (a Johnny-come-lately)? If he has a C or an M next to his name, then his (or her) selection of foods is counted correctly, but the total number of guests stays the same, at 250. The total number of guests is not increased. How do I make the total count correct?


Thanks once again for the solution, and any additional information you might add.

Hi abraxis,

Sorry for the delay. Had a full day yesterday.

Yes, you can use either one. They were just two different options.

As for the new user, the Countif will capture the new input as I used the whole column reference.

=COUNTIF(B:B,K2)

For the pivot table, you would need to take two actions.

1) Change the source data to include entire ranges

Analyze >> Data >> Change Data Source >> Sheet1!$A:$B

2) Once new data is loaded, you will need to right click the PT and select refresh

P.S. You could also look into a dynamic named range

How to Create a Pivot Table with Expanding Data Ranges -

Thank you very much. The answer was right on the money and very helpful. And seeing the correct answer and approache made the whole topic of counting functions very understandable.
Abradaxis