Excel – What formula would allow incrementing a number each time a certain name appears in a column

microsoft excel

How do I add a list of names to a spreadsheet and each time a specific name appears add the number 10 to cell. For example, I would put a name like Jones in A1 then Jones in A5. And in D5 it would have the number 20. If I then add Jones in B2 then D5 would say 30. Add 10 each time Jones appears.

What formula would I use?

Best Answer

You can use the COUNTIF function to count the number of times a value appears in a range.

If you simply want to count the number of times "Jones" appears in a given range and multiply that by 10, you can use the formula:

=COUNTIF("A1:C99","Jones")*10

If your names are in column A starting at row 1, and you want the numbers in column B to count the number of times the name in column A is found, enter the following formula in cell B1:

=COUNTIF(A$1:A1,A1)*10

Now copy that cell and paste it in the rest of column B. when you paste the formula, A$1 will remain the same, but A1 will be changed to include the row number where the formula is pasted. For example, the formula in cell B5 will be:

=COUNTIF(A$1:A5,A5)*10

The formula will count the number of times the name in column A occurs in all of column A from row 1 through the current row, then multiply that by 10.

enter image description here