How to combine duplicate data and sum totals of those rows.

I have a sheet that we track data for on a monthly basis for the same items (example below). I would like to display a "top impressions" and "top clicks" widget on my dashboard, but cannot figure out how to show this data without it being duplicated over the 12 times (once for each month). I know a report will need to be involved, but I cannot seem to envision this and whether to start with a report or start with a second sheet to pull the data into where I can combine with formulas. I greatly appreciate any insight.

Each month is a parent row with all the data below it. The Category and Date columns are helper columns for other various widgets/tasks on the dashboard and are typically hidden.

Find more posts tagged with

Formulas

Reports

Dashboards

Accepted answers

All comments

Mike TV

@Megan Yaussi

Here's my sample sheet:

Here's the Sheet Summary cells that I created:

Jan Top Impressions formula:

=INDEX(CHILDREN([Project Name]$2), MATCH(MAX(CHILDREN([Total Impressions]$2)), [Total Impressions]$3:[Total Impressions]$7), 1) + " - " + MAX(CHILDREN([Total Impressions]$2))

Jan Top Clicks formula:

=INDEX(CHILDREN([Project Name]$2), MATCH(MAX(CHILDREN([Total Clicks]$2)), [Total Clicks]$3:[Total Clicks]$7), 1) + " - " + MAX(CHILDREN([Total Clicks]$2))

Feb Top Impressions Formula:

=INDEX(CHILDREN([Project Name]$8), MATCH(MAX(CHILDREN([Total Impressions]$2)), [Total Impressions]$9:[Total Impressions]$13), 1) + " - " + MAX(CHILDREN([Total Impressions]$8))

Feb Top Clicks Formula:

=INDEX(CHILDREN([Project Name]$8), MATCH(MAX(CHILDREN([Total Clicks]$2)), [Total Clicks]$9:[Total Clicks]$13), 1) + " - " + MAX(CHILDREN([Total Clicks]$8))

I opted to make a lot of absolute references in case things get inserted on the sheet. You could try to reference CHILDREN instead, if that works.

You can then display the summary cells on a dashboard in a couple of ways...individually or all combined, etc. Like so:

Megan Yaussi

@Mike TV This is really helpful to get started. In the end. I'll need a single list from the "Project Name" column with no duplicates and a sum of all numbers from either the "total impressions" or "total clicks" columns. From there, those single numbers can be displayed on the dashboard to indicate the running total from the year instead of broken out by month. Sorry for the confusion.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.