Can I use SUMIF??

[palmer41420]

I have a spreadsheet that contains several thousands of rows of data for my clients. I would like to combine the rows with the same "Unique ID" into one row but sum the data from one column("Absences") to be displayed in the new row with all of the original data. The table has this format:

School Grade Unique ID Ethnicity Gender Absences
WES 5 4566 W M 2
WES 5 4566 W M 4
FLE 1 4243 B M 1
FLE 1 4243 B M 1
FLE 1 4243 B M 3
UGE 4 5464 H F 2
UGE 4 5464 H F 3
UGE 4 5464 H F 1

The table I need to return would look like this:
School Grade Unique ID Ethnicity Gender Absences
WES 5 4566 W M 6
FLE 1 4243 B M 5
UGE 4 5464 H F 6

I feel like I am stuck between SUMIF and VLOOKUP. Is there a way to do this with a formula? I know this can easily be accomplished with a Pivot Table but I need the data to be in table format so it can be filtered and sorted easily by beginner level excel users.

 

[Lee.]

I have a spreadsheet that contains several thousands of rows of data for my clients. I would like to combine the rows with the same "Unique ID" into one row but sum the data from one column("Absences") to be displayed in the new row with all of the original data. The table has this format:

School Grade Unique ID Ethnicity Gender Absences
WES 5 4566 W M 2
WES 5 4566 W M 4
FLE 1 4243 B M 1
FLE 1 4243 B M 1
FLE 1 4243 B M 3
UGE 4 5464 H F 2
UGE 4 5464 H F 3
UGE 4 5464 H F 1

The table I need to return would look like this:
School Grade Unique ID Ethnicity Gender Absences
WES 5 4566 W M 6
FLE 1 4243 B M 5
UGE 4 5464 H F 6

I feel like I am stuck between SUMIF and VLOOKUP. Is there a way to do this with a formula? I know this can easily be accomplished with a Pivot Table but I need the data to be in table format so it can be filtered and sorted easily by beginner level excel users.

SUMIF will work fine, and set the criteria as the unique ID

 

[palmer41420]

SUMIF will work fine, and set the criteria as the unique ID

Would you mind writing the formula for me? I can't seem to get it to work.

 

[Lee.]

Would you mind writing the formula for me? I can't seem to get it to work.

=SUMIF($C$2:$C$9,C16,$F$2:$F$9)

C2:C9 = Unique ID range on original data
C16 = Unique ID on 'summary table'
F2:F9 = Absence range on original data

 

[palmer41420]

Thanks Lee,

I replied earlier but it did not post. I figured it out after I realized SUMIF would work if I used a separate worksheet to remove duplicates and then used SUMIF to pull the data in from the original worksheet. I forgot my $s though so your formula reminded me and the data is now pulling correctly on a small sample. When I use the formula with 47,000 rows of data it is VERY SLOW processing but it works!