Excel, find and count each occurence of a name is column A then read a value from column B

Question

Hi all,

I need a formula that is beyond me and my Excel skills, I need to insert the number of times a match is found in column A into column C and then insert the total number of days for that person in column D. Can anyone help?

Many thanks

Answer 1

In cell C2:

=COUNTIF(A$2:A$6,A2)

In cell D2:

=SUMIF(A$2:A$6,A2,B$2:B$6)

See also:

• COUNTIF
• SUMIF

Answer 2

Have you considered using a pivot table? It's a little bit overkill but greatly simplifies what you want to do. Assuming your using Excel 2007:

1. Select the range of data including column labels.
2. Go to Insert->[Tables]->PivotTable.
3. In the dialog box that appears, select Existing Worksheet, choose a cell a click OK.

At this point, the PivotTable pane appears with your field names in one box and four other labeled boxes below.

1. Drag Name to the Row Labels box.
2. Drag Name to the Values box.
3. Drag Days to the Values box.

You're done!