View original (French)

Get cumulative sum in Excel

Solved
MALCOMDU97 -  
 MALCOMDU97 -
Hello,

I want to do the following in Excel:

From the following accounting balance:

Example:

Account number - label - debit - credit

101000 capital 10000
604000 purchase of services 5000
613000 rent 15000
616100 insurance mutual risk 6000
616110 loan insurance 500
706000 service provision 150000

In a formula I would like to sum accounts, for example from the root 616

=get_cumul(616100;616110)

So in short I need a formula that, when I type in a cell (616), returns the sum of the debit amounts for all accounts whose root is 616

Here we should obtain 6500 as the result.

Thanks in advance

Configuration:
Windows 7 / Internet Explorer 9.0

3 answers

  1.
    Mytå Posted messages 4246 Registration date   Status Contributor Last intervention   957
     
    Hello the forum

    Cell range to adapt 
    =SUMPRODUCT((LEFT(A2:A500,3)="616")*(C2:C500))

    Mytå
    --
    What pretension to claim that computing is recent
    Adam and Eve already had an Apple! [MsProject 2003(FR), Excel 2003-2007(FR)]
    1
    1. MALCOMDU97
       
      thank you MYTA for this answer it works for the root yes, but I wanted this: look at the file at this link http://cjoint.com/?BJowyr3r88F and you will understand my problem.

      Thank you in advance.
      0
  2. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    Hello.

    First, in column E display the account number using the formula =LEFT(A:A,3), then calculate the partial sum with the SUMIF function referencing that column.
    If you enter what you call the "root" (but in France it is the "account number") in H4, the formula will be =SUMIF(E:E;H4;B:B)

    Best regards.
    --
    That's a good retirement! Especially in the Caribbean ... :-)
    ☻ Raymond ♂
    0
    1. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
       
      1) Avoid creating new words, such as get cumul.

      2) Every time you ask a question related to a software, be sure to specify the name and the version! In the current case it has no impact; but if I had proposed the SOMME.SI.ENS() function, it only exists since Excel 2007 ...

      3) Get into the habit of accompanying your explanations with a file, even if incomplete. For this, create a link with https://www.cjoint.com/ and paste it into your message. This way answers can rely on clear line and column references.
      0
    2. MALCOMDU97
       
      Thank you for this early response, I’ve taken note of your small comment, so a thousand apologies, here is a link where you will find an Excel file http://cjoint.com/?BJowyr3r88F here I’ve started to lay out my problem, thanks in advance for your help.
      0
    3. MALCOMDU97
       
      I am on Excel 2010 for info.
      0
    4. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
       
      Here is what I proposed to you: https://www.cjoint.com/?BJpc2q3H8hX

      Your request to write multiple criteria in a single cell is not in accordance with the functioning principles of a spreadsheet.
      Most of the financial statements clearly display the 3 digits of the account number and the sub-account number with more than 3 digits, and they provide the totals by account before giving them by class.

      Best regards.
      0
    5. MALCOMDU97
       
      Thank you Raymond for this solution, I will study it closely for my chart.
      0
  3. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello MALCOMDU97, the participants,

    To help you enter the number of characters you choose, you can use this formula that gives you debits and credits: 

    =SOMMEPROD((GAUCHE($A$2:$A$499;NBCAR(H8))=TEXTE(H8;"@"))*($C$2:$D$499))

    Your workbook with the test in yellow:

    https://www.cjoint.com/?BJoxbcaTC00
    --

    Always zen
    Perfection is not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry
    0
    1. MALCOMDU97
       
      Thank you GBININFORME I think your solution really helps me, this formula solves my problem regarding the account root and the sub-account, so I could move forward with setting up my table, thanks again.
      0