)
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 custom report builder doesn’t include COUNT functions in formula fields, making it impossible to combine closed won and closed lost deal counts directly within the platform.
Here’s how to work around this limitation and create the combined deal metrics you need using live HubSpot data in spreadsheets.
Import HubSpot deals data into spreadsheets for advanced counting using Coefficient
Coefficient solves this problem by importing live HubSpot deals data into Google Sheets or Excel where you can use native spreadsheet functions. This eliminates HubSpot’s formula field limitations while providing automatic data updates.
How to make it work
Step 1. Connect HubSpot and import deals data.
Install Coefficient and connect your HubSpot account through the sidebar. Import all deals data with fields like Deal Stage, Close Date, and Deal Owner. Set up automatic refresh scheduling (hourly or daily) to keep your data current.
Step 2. Apply filters for closed deals.
Use Coefficient’s filtering capabilities to focus on closed deals by setting Deal Stage to “Closed Won” OR “Closed Lost”. You can apply up to 25 filters with AND/OR logic to get exactly the data you need.
Step 3. Create COUNTIF formulas to combine deal counts.
Use spreadsheet formulas like =COUNTIF(Deal_Stage_Column,”Closed Won”)+COUNTIF(Deal_Stage_Column,”Closed Lost”) to get your total closed deals count. For more complex scenarios, try =COUNTIFS(Stage_Column,”Closed*”,Owner_Column,A2) to count by specific criteria.
Step 4. Set up automated alerts and dashboards.
Create dynamic dashboards with combined metrics that update automatically. Set up Slack or email alerts when total closed deals hit specific thresholds so your team stays informed of important milestones.
Get the deal counting capabilities HubSpot can’t provide
This approach gives you sophisticated calculation capabilities that surpass HubSpot’s native custom report builder. You can create the combined deal metrics you need while maintaining live data connections. Try Coefficient to start building better deal reports today.
