How to Aggregate Social Media Engagement by Content Type in Excel

Consolidating disparate social media engagement metrics across multiple campaigns often leads to analytical paralysis. Before aggregating these figures, organizations typically map their performance back to standard funding sources and marketing budgets to justify ROI. Implementing a structured formulaic approach grants marketing teams immediate, actionable clarity on content performance.

Stipulation: This methodology requires consistent taxonomy and data tagging across your raw export sheets to ensure accuracy.

For instance, utilizing =SUMIFS(Engagement_Column, Type_Column, "Video") allows you to instantly isolate high-performing video assets from static posts.

Below, we will explore the specific nested Excel formulas and setups required to fully automate your social media aggregation dashboard.

In the fast-paced world of digital marketing, data is the compass that guides content strategy. Social media managers and content creators constantly ask themselves: What type of content performs best? Should we invest more in video production, write longer text-based posts, or focus on sharing high-quality images and blog links? To answer these questions objectively, you must aggregate and analyze your engagement metrics by content type.

While specialized social media analytics tools exist, Microsoft Excel remains one of the most powerful, flexible, and accessible tools for customizing this data analysis. By mastering a few key Excel formulas, you can transform a raw export of social media metrics into an organized, actionable dashboard. This guide will walk you through how to use Excel formulas-ranging from the foundational SUMIFS to the advanced SUMPRODUCT and modern dynamic arrays-to aggregate social media engagement by content type.

Setting Up Your Social Media Data Structure

Before writing any formulas, it is crucial to structure your raw data correctly. Typically, you can export your social media performance data from platforms like LinkedIn, Facebook, Instagram, or X (formerly Twitter) as a CSV file. Once imported into Excel, arrange your dataset so that each row represents an individual post, and each column represents a specific attribute or metric.

Below is an example of how your raw data table (which we will refer to as range A1:F7) should look:

In this structure, Column F (Total Engagement) is calculated using a basic addition formula for each row: =SUM(C2:E2). Once your raw data is organized, you can set up a summary table elsewhere in your sheet to aggregate these metrics by content type.

Method 1: The Essential SUMIFS Formula

The SUMIFS function is the most reliable workhorse for aggregating data based on specific criteria. It adds all numbers in a specified range that meet one or more conditions.

The Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Applying SUMIFS to Your Summary Table

Let's say you want to build a summary table in columns H and I that lists each unique content type and its corresponding aggregate likes:

To calculate the total likes for "Video" (located in cell H2), enter the following formula in cell I2:

=SUMIFS($C$2:$C$7, $B$2:$B$7, H2)

How it works:

• $C$2:$C$7 is the sum_range-the cells containing the likes we want to add together. We use absolute references (with the $ signs) so the range doesn't shift when we drag the formula down.
• $B$2:$B$7 is the criteria_range1-the cells containing the content types we want to evaluate.
• H2 is the criteria1-the specific content type we are filtering by ("Video"). As you copy this formula down to I3, I4, and so on, this relative reference will change to point to "Image", "Carousel", etc.

You can repeat this process for Shares, Comments, and Total Engagement by changing the sum_range accordingly. For instance, to aggregate total engagement (Column F) for each content type, use:

=SUMIFS($F$2:$F$7, $B$2:$B$7, H2)

Method 2: Multi-Column Aggregation with SUMPRODUCT

What if your raw export does not have a helper column for "Total Engagement" (Column F), and you do not want to create one? You can calculate the overall sum of likes, shares, and comments for a specific content type in a single cell using the highly versatile SUMPRODUCT formula.

The Syntax

=SUMPRODUCT(array1, [array2], [array3], ...)

Applying SUMPRODUCT for Social Media Analytics

To sum up all metric values across Columns C, D, and E simultaneously for the "Video" content type, use this formula:

=SUMPRODUCT(($B$2:$B$7=H2) * ($C$2:$E$7))

How it works:

• ($B$2:$B$7=H2) creates an array of Boolean values (TRUE/FALSE) checking if each row matches "Video". For our sample dataset, this evaluates to: {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE}.
• When multiplied by another numerical array, Excel coerces TRUE to 1 and FALSE to 0.
• ($C$2:$E$7) is the 2D array of metrics (Likes, Shares, Comments).
• Excel multiplies the Boolean array by each column in the metrics range row-by-row. If a row does not match "Video", its metrics are multiplied by 0, rendering them 0. If it does match, the metrics are multiplied by 1, keeping their original values.
• Finally, SUMPRODUCT sums all these products together, giving you the total engagement across all three columns without requiring an intermediate total column.

Method 3: Dynamic Arrays (Excel 365 and 2021+)

If you are using a modern version of Excel, you can build a completely dynamic reporting table that updates automatically when new content types are added to your raw data. This eliminates the need to manually type or copy unique content types into your summary table.

Step 1: Dynamically Extracting Unique Content Types

In your summary worksheet, select an empty cell (e.g., K2) and type the following formula to extract a unique list of content types from your raw data:

=UNIQUE(B2:B7)

This formula "spills" the unique values down the column automatically, creating a dynamic array. If a new content type like "Poll" is added to your data source, it will instantly appear in this list.

Step 2: Summing Metrics with the Spill Operator

To aggregate the total engagement for this dynamically generated list, reference the spilled range using the hashtag operator (#). In cell L2, write:

=SUMIFS(F2:F7, B2:B7, K2#)

The # tells Excel to apply the SUMIFS calculation to every cell in the dynamic array starting at K2. This ensures your summary values always align perfectly with your content types, even as rows are added or removed.

Best Practices for Social Media Auditing in Excel

To ensure your Excel formulas operate flawlessly and yield accurate business insights, keep these practical tips in mind:

• Clean Your Data: Social media exports can contain trailing and leading spaces (e.g., "Video " instead of "Video"). This will cause your formulas to mismatch. Use the =TRIM() function to clean up your content categorization columns before running aggregation formulas.
• Use Excel Tables: Instead of referencing raw cell ranges (like $B$2:$B$7), convert your raw dataset into an official Excel Table (shortcut: Ctrl + T). This allows you to use structured references. Your formulas will look cleaner: =SUMIFS(SocialData[Total Engagement], SocialData[Content Type], H2), and they will automatically scale as you paste new export data at the bottom of the table.
• Calculate Average Engagement Rate: Raw totals can sometimes be misleading if you post one type of content far more frequently than another. To find the average performance per post type, combine your formulas to find the mean:

  =AVERAGEIFS(F2:F7, B2:B7, H2)

  This helps you identify efficiency, rather than just raw volume.

Conclusion

Aggregating social media data by content type is a critical step in refining your digital marketing strategy. By moving away from manual calculations and utilizing Excel's robust formula engine-whether through standard SUMIFS, sophisticated SUMPRODUCT logic, or state-of-the-art dynamic arrays-you gain the ability to generate rapid, reliable, and automated insights. Apply these formulas to your next social media audit to clearly identify what drives your audience to click, share, and engage.