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 – 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

    • Zoholics Europe 2025: Your Ultimate Data Analysis (Zoho Analytics) Workshop Experience

      Why should you attend? This year, Zoholics Europe 2025 is putting data analysis centre stage. With a dedicated workshop designed to answer all your data-related questions, you’ll gain practical skills, real-time solutions, and expert insights that you

    • [Webinar] Evolving BI & Analytics in the Age of AI

      Artificial intelligence is redefining how data is collected, analyzed, and leveraged across industries. As businesses strive to become more agile and insight-driven, traditional BI and analytics must transform to meet new demands. AI-first organizations