)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
HubSpot’s formula builder beta explicitly lacks COUNT and SUM aggregation functions, making it impossible to sum closed won and closed lost deals natively within the platform’s reporting tools.
Here’s how to get full spreadsheet functionality with live HubSpot data integration to create the deal summations your sales team needs.
Access native SUM functions with live HubSpot data integration using Coefficient
Coefficient provides the ideal solution by offering full spreadsheet functionality with live HubSpot data integration in spreadsheets . This eliminates dependency on HubSpot’s limited formula capabilities while providing enterprise-grade summation functionality.
How to make it work
Step 1. Import HubSpot deals with scheduled refresh.
Set up automatic data import with refresh options from hourly to daily. Apply filters for closed deal stages during import to focus your summation calculations on relevant deal data.
Step 2. Create native SUM functions for closed deals.
Use standard spreadsheet functions like =SUMIF(Stage_Column,”Closed Won”)+SUMIF(Stage_Column,”Closed Lost”) for deal counts, or sum actual deal amounts for total closed revenue metrics. Build dynamic calculations that automatically include new closed deals as data refreshes.
Step 3. Build multi-criteria summation with SUMIFS.
Create complex summations using SUMIFS for criteria like date ranges, deal owners, or pipeline sources. Use formulas like =SUMIFS(Amount_Column,Stage_Column,”Closed*”,Owner_Column,A2) to sum by specific combinations that HubSpot’s limited formula builder cannot handle.
Step 4. Set up conditional formatting and automated alerts.
Use conditional formatting to highlight significant changes in summed totals. Configure automated alerts when summed totals exceed targets, providing proactive monitoring that HubSpot’s static formulas cannot deliver.
Step 5. Create advanced use cases with rolling calculations.
Build rolling 30-day sums using date criteria, create year-over-year comparison calculations, and generate automated reports with summed metrics delivered via Slack or email. These advanced capabilities far exceed what HubSpot’s formula builder can provide.
Step 6. Export calculated sums back to HubSpot (optional).
Push your calculated sums back to HubSpot as custom properties if needed for native dashboard display, combining spreadsheet calculation power with HubSpot’s familiar interface.
Get enterprise-grade summation functionality beyond HubSpot’s limitations
This approach eliminates dependency on HubSpot’s limited formula capabilities while providing sophisticated summation functionality that can handle both count and value aggregations across any criteria combination. Start building the deal summations your team needs.
