sum problem

met

New Member
Joined
Apr 10, 2002
Messages
9
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
On 2002-04-16 09:39, met wrote:
is there a simplier way? thanks!

met /board/images/smiles/icon_smile.gif:):)

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
 
Upvote 0
what if i added another criteria, i want it by for ex, by sales agent and weekly? how would i do that?

met :)
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top