Excel – Filtering a column and summing values (in Excel)

microsoft excelworksheet-function

I have an Excel worksheet, pieces of which are shown below:

I want to filter column D so they're unique (i.e., cat, dog, fish) and sum the values corresponding to those values like this:

enter image description here

sheet screenshot

I'm familiar with filtering ranges in Excel, formulas, and such functions as COUNTIF(), but I can't make this work.

Best Answer

Listing distinct values, and summing values depending on data in another column can be done with the formulas below. Using your sample data, the results are shown in the following table.

enter image description here

To find the distinct values in column D, type this formula in G2 and fill down:

=IFERROR(INDEX($D$2:$D$9,MATCH(0,INDEX(COUNTIF($G$1:G1,$D$2:$D$9),0,0),0)),"")

Note that there MUST be a row above the first row of data for this formula to work correctly. I've used a row with column names here, but it could be an empty row.

To get the sum of the counts for each distinct value, type this formula in H2 and fill down:

 =SUMIF(D$2:D$9,G2,E$2:E$9)

A good explanation of how to list/count unique and distinct values can be found here. And some great examples using the SUMIF() function are given here. I hope this helps and good luck.

Related Question