sum problem

[met]

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 ]

[Aladin Akyurek]

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 ]

[met]

is there a simplier way? thanks!

met

[Dave Patton]

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)

[Yogi Anand]

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

[Aladin Akyurek]

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

[met]

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

met

[Dave Patton]

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?

[met]

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 ]

[Aladin Akyurek]

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