View original (French)

Excel - Sum by Quarter

Solved
c92 -  
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   -
Hello,

I have an Excel table with an invoice date in column A and the amount in column B.
I would like to automatically calculate (without subtotals) the amount of invoices by quarter in an adjacent table.
I think I will use the SUMPRODUCT function, but I'm not sure what conditions to specify for the quarters.
Thank you for your help.

2 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    for a date range A1 to A1000 and amounts from B1 to B1000
    try with:
    1st quarter:
    SUMPRODUCT(MONTH(A1:A1000)<4)*(B1:B1000)
    2nd quarter
    SUMPRODUCT(MONTH(A1:A1000)>3)*(MONTH(A1:A1000)<7)*(B1:B1000)
    3rd quarter
    SUMPRODUCT(MONTH(A1:A1000)>6)*(MONTH(A1:A1000)<10)*(B1:B1000)
    4th quarter
    SUMPRODUCT(MONTH(A1:A1000)>9)*(B1:B1000)
    sincerely
    Let’s ask ourselves if we are not the only ones who understand what is being explained?
    2
    1. c92
       
      Hello,
      it works very well for the first T but not for the others, do you have any idea... I've tried to modify it but to no avail.
      0
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Hello
      here's a model file with the SUMPRODUCT solution and a solution with SUMIF + an intermediary column (I placed it to verify the totals).
      In principle, SUMPRODUCT works without any issues
      https://www.cjoint.com/?AKDhtxcljVT

      if you're having trouble, and if possible, upload a file to the site where you found this one and return to place the link provided by the site in a subsequent message

      note: in my initial message, I forgot a parenthesis right after the text SUMPRODUCT
      SUMPRODUCT((......
      It might be from this error (on my part) that your problem arises.
      good luck

      looking forward to hearing from you
      best regards
      1
  2. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello,

    Without formulas to write, you can do this very quickly with a Pivot Table (the example is by month and year, but you can choose by quarter)

    https://www.cjoint.com/?3Kzscp4qLbd
    --
    Michel
    0