How to combine the duplicate text values in one column and count/sum the values in other columns

Linda Xie

Hello All,

I need some helps to achieve a goal between the source data sheet and a different output in destination sheet. Basically I am trying to combine duplicate texts in column "Facility Name" from source sheet shown below to a new sheet and group the value of sums or counts in totals for that combined text value. I don't necessarily need to keep the ID column in the New Sheet. I know there must be the formulas that can solve these type of inquires.

Many thanks in advance!

Source Data sheet

New Sheet Output

Paul Newcome

You could create a row report, group by Facility Name, then use a report summary field to get your totals.

Linda Xie

Hi Pau,

I created a report and also grouped it by Facility Name, it works to show the combination of facility names without duplicates in that name field. However when I tried to sort the report by Invoice Amt to show top 10 payments only, it does not give me the result I want, meaning the sorting function is working.

Any suggestions or ideas?

Genevieve P.

Hi @Linda Xie

When Reports are Grouped, the Grouping will be sorted alphabetically, which means your Report will be sorted by Facility Name. Then if you sort rows, those rows will sort underneath their Facility but won't rearrange the grouping.

If you want to do this in a second sheet instead, you can use a SUMIFS Function to SUM together the values in one column based on criteria:

=SUMIFS({Invoiced Amount}, {Facility Name}, [Facility Name]@row)

See: Create cross sheet references to work with data in another sheet

Cheers,

Genevieve