Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: sum problem

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    pls help. i dont know what to do with this. i need total of weekly data.

    Col A Col B Col C
    04-1 3 04/1-7/02 = 6
    04-7 3 04/8-14/02 = 7
    04-12 2 04/15-21/02 = 5
    04-13 5 04/22-28/02 = 0
    04-16 4 04/29-30/02 = 8
    04-20 1
    04-29 6
    04-30 2

    thanks in advance

    met


    [ This Message was edited by: met on 2002-04-14 07:04 ]

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-14 07:03, met wrote:
    pls help. i dont know what to do with this. i need total of weekly data.

    Col A Col B Col C
    04-1 3 04/1-7/02 = 6
    04-7 3 04/8-14/02 = 7
    04-12 2 04/15-21/02 = 5
    04-13 5 04/22-28/02 = 0
    04-16 4 04/29-30/02 = 8
    04-20 1
    04-29 6
    04-30 2

    thanks in advance

    met


    [ This Message was edited by: met on 2002-04-14 07:04 ]
    Lets say that your data start at row 2.

    In C2 enter and copy down:

    =WEEKNUM(A2)

    Make a unique list of week numbers in D from D2 on.

    In D1 enter:

    =MATCH(9.99999999999999E+307,A:A)

    In E2 enter and copy down:

    =SUMIF(OFFSET($C$2,0,0,$D$1-1,1),D2,OFFSET($B$2,0,0,$D$1-1,1))

    -1 in the $D$1-1 takes into account the fact that there is 1 row before the row where the actual data start.

    Note. The above scheme is devised for the calendar weeks. WEEKNUM, if needed, can be made available by checking the Analysis Toolpak in Tools|Add-Ins.


    [ This Message was edited by: aladin akyurek on 2002-01-14 13:49 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    is there a simplier way? thanks!

    met

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,426
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    In an available column (or insert a column) calculate the weeknum.
    I used column C =WEEKNUM(A2,2)

    Enter a list of number like
    14
    15
    16
    18
    In say Column D

    Use Sumif

    =SUMIF(C2:C9,D2,B2:B9)

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi met:
    If I understood your requirement correctly, here is another approach ...
    since you already have your weeks laid out in column C, use the formula

    =SUMIF(C:C,"04/1-7/02",E:E) ... to get 6
    for the week of "04/1-7/02"

    HTH

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-16 09:39, met wrote:
    is there a simplier way? thanks!

    met [img]/board/images/smiles/icon_smile.gif[/img]
    I avoided a simpler SUMIF formula for a reason. I just reasoned that you will be adding more dates to column C. The formulas will respond to the additions appropriately, without any need for adjusting them. Using whole columns in SUMIF is inefficient. Using the actual ranges will require changing/editing the formula. It's up to you which you'd prefer to use.



    Aladin

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    what if i added another criteria, i want it by for ex, by sales agent and weekly? how would i do that?

    met

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,426
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    Search the site for examples of using
    SumProduct.

    You could have included a concise clear example and your expected results.

    What columns include real dates (not text)?
    Do you want the formula to determine the weeks or are week numbers available?


  9. #9
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    my data includes real dates and collection payment of sales agent every week. my summary would look like this...
    04/1-7/02 04/08-14/02 04/15-21/02
    Agent A 500.00 0 350.00
    Agent B 1000.00 500.00 0
    Agent C 0 0 1500.00

    i hope this would help...thanks

    met

    m really sorry...i cant align the columns!

    [ This Message was edited by: met on 2002-04-20 06:06 ]

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-20 06:05, met wrote:
    my data includes real dates and collection payment of sales agent every week. my summary would look like this...
    04/1-7/02 04/08-14/02 04/15-21/02
    Agent A 500.00 0 350.00
    Agent B 1000.00 500.00 0
    Agent C 0 0 1500.00

    i hope this would help...thanks

    met

    m really sorry...i cant align the columns!

    [ This Message was edited by: met on 2002-04-20 06:06 ]

    I'll assume that you have your data in A to C in a sheet called Data like the sample that follows:

    {"Date","Agent","Amount";
    37347,"A",3;
    37353,"A",3;
    37358,"B",2;
    37359,"C",5;
    37362,"A",4;
    37366,"B",1;
    37375,"B",6;
    37376,"C",2}

    and the actual data start at row 2. The above sample is in A1:C9.

    In D1 enter: Week
    In D2 enter and copy down:

    =WEEKNUM(A2)

    Note 1. WEEKNUM is available thru Analysis Toolpak (which, if needed, can be activated via Tools|Add-Ins).

    I'll assume that the data area grows by new additions. Therefore we are going to define dynamic name ranges which you can also use in any other analysis of the data that you might want to carry out.

    Activate Insert|Name|Define.
    Enter NumRecs as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =MATCH(9.99999999999999E+307,Data!$A:$A)

    Note 2. The formula refers explicitly to the column that houses dates.

    Activate Add. (Don't leave yet the Define Name window.)

    Enter DataRecs as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =NumRecs-(ROW(Data!$A$2)-1)

    Activate Add. (Don't leave yet the Define Name window.)
    Enter DATES as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Data!$A$2,0,0,DataRecs,1)

    Activate Add. (Don't leave yet the Define Name window.)

    Enter AGENTS as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Data!$B$2,0,0,DataRecs,1)

    Activate Add. (Don't leave yet the Define Name window.)

    Enter AMOUNTS as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Data!$C$2,0,0,DataRecs,1)

    Activate Add. (Don't leave yet the Define Name window.)

    Enter WEEKS as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =OFFSET(Data!$D$2,0,0,DataRecs,1)

    Activate OK.

    Note 2. You can also give a name the whole area, DTable using the foregoing procedure with the formula:

    =OFFSET(Data!$A$2,0,0,DataRecs,4).

    Go now to a worksheet called Summary.

    Enter in row 2 from B2 on: 04/1-7/02, 04/08-14/02, 04/15-21/02, etc.

    Enter in B3:

    =WEEKNUM(MIN(DATES))

    Enter in C3 and copy across:

    =B3+1

    In B4 enter and copy across:

    =COUNTIF(WEEKS,B3)

    This gives you a weekly transaction count.

    In B5 enter and copy across:

    =SUMIF(WEEKS,B3,AMOUNTS)

    This gives you a weekly transaction total.

    Enter a unique list of agents from A6 on downwards (You can do this with Advanced Filter or manually).

    In B6 enter:

    =SUMPRODUCT((AGENTS=$A6)*(WEEKS=B$3),AMOUNTS)

    This gives you weekly totals for each agent.

    Copy this first across then down as far as needed.

    Another method to get such a summary is by using Pivot Tables.

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •