Share via

How to tell excel to keep summing up values until a condition is met

Anonymous
2024-09-11T04:12:21+00:00

Hi Everyone!

Date Sales Amount
2/6/2024 23
2/6/2024 24
3/6/2024 26
3/6/2024 68
4/6/2024 27
4/6/2024 68
5/6/2024 35
5/6/2024 35
5/6/2024 86

I want to check the Date when the cumulative "Sales Amount" hit 30000 counting from top to bottom. Appreciate for the help to sort out this issue. Sumif Sales Amount hit 30000, then Date = 18/6/2024.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-09-11T05:54:16+00:00

    Hi, Chong Siao Chuen

    Thank you for using Microsoft products and posting them to the community.

    You can create a secondary column:

    In cell C2 (next to the first “Sales Amount”), enter =B2.

    In cell C3, enter =C2+B3.

    Drag this formula down next to the Sales Amount column to create a cumulative sum.

    Determine the target date:

    Once you have a cumulative sum, find the first cell in column C that has a value of 30,000 or more.

    The corresponding date in column A is the date you are looking for.

    I hope the above information can help you. Feel free to send a message if you need further help.

    Best wishes

    Aiden.C - MSFT |Microsoft Community Support Specialist

    0 comments
  2. Ashish Mathur 100.8K Reputation points Volunteer Moderator
    2024-09-12T00:14:54+00:00

    Hi,

    In cell E2, enter this formula

    =XLOOKUP(TRUE,SCAN(0,B2:B10,LAMBDA(a,I,SUM(a,I)))>=100,A2:A10)

    Hope this helps.

    0 comments