Is it possible to have an Aggregate or a formula data column that only sums subtotals?

Is it possible to have an Aggregate or a formula data column that only sums subtotals?

So I am trying to make a data columns that would only sum the subtotal.

let me explain, I want to sums the hours worked of employes. I also need to split the hours when they reach 40 hours into another column. I can make this work perfectly with formula or aggregate the only thing that doesnt work is the sum part.

If i go formula i do something like this: 
if("7. Total a payer" > 40,40,"7. Total a payer")
This doesn sum and the grand total is 40

If i go aggregate like this: 
if(sum("PRO - Facturation Query"."Total to pay")>40,40,sum("PRO - Facturation Query"."Total to pay"))
The sums work great if we only have 1 row as soon as I add data in the row section that as more than 1 the grand total doesnt work anymore.

i also tried using fixed_groupby and other like this I just can't get it to work.

So basicaly if I add a row that as multiple data it sums up all of them and I dont want to.
If you look at this image you see the 2 columns i made the aggregate is the one named sss and the formula is the one regular hours




Now if i add a row that as more data you see the data column ssss just whent all crazy

    • Sticky Posts

    • What's New in Zoho Analytics - November 2025

      We're thrilled to announce a significant update focused on expanding your data connectivity, enhancing visualization capabilities, and delivering a more powerful, intuitive, and performant analytics experience. Here’s a look at what’s new. Explore What's

    • What's New in Zoho Analytics - October 2025

      Hello Users! We're are back with a fresh set of updates and enhancements to make data analysis faster and more insightful. Take a quick look at what’s new and see how these updates can power up your reports and dashboards. Explore What's New! Extreme

    • What’s New in Zoho Analytics – September 2025

      Hello Users!! In this month’s update, we’re raising the bar across multiple touchpoints, from how you bring in data, plan and track projects to how you design and brand your dashboards. We’ve added the all-new Gantt chart for project visualization, expanded

    • Announcing Agentic AI - Ask Zia!

      We are delighted to roll out the new agentic AI capabilities in Ask Zia, where every stage of the BI workflow is assisted by AI. With a human-in-the-loop approach, Ask Zia ensures that you’re in command of the decision, while AI handles the complexity.

    • Invitation-Based User Access in Zoho Analytics

      Hello everyone, We’re rolling out an important update on how users are added to your Zoho Analytics Organization and Workspaces. Previously, when admins added users, they were automatically added to the organization. Moving forward, to improve security