CODEWITHSUNDEEP
excel
Jamie
Jamie

Reputation: 2565

Find and count each occurence of a name is column A then read a value from column B

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?

Upvotes: 3

Views: 15428

Answers (2)

gvkv
gvkv

Reputation: 1916

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!

Upvotes: 2

kennytm
kennytm

Reputation: 523514

In cell C2:

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

In cell D2:

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

See also:

Upvotes: 3

Related Questions