Help adding multiple instances based on date.

[GazNicki]

Hi

Please bear with me on this one.

I have a large spreadsheet (layout below) which tracks the sale of items (listed down in column A) by customer. Each customer takes a range of items, so therefore there are multiple instances of these items in Column A.

In row 1, starting at cell B2 is the week ending date. (Cell A2 contains the weekending date as a number currently too). The sheet spans across from B2:BC2 as the weeks go along the top.

On a separate worksheet in the same workbook, I want to sum up all the instances of an item, but specific to one week. I can use SUMIF or SUMPRODUCT, but that requires me to define a range which is static, but I want the week to change.

I have tried to use SUMIFS in order to define two criteria, but I am presented with a #VALUE ! error everytime. I am assuming this is because one of my ranges is vertical and the other horizontal. Is this the case, or have I done something wrong?

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]43106[/TD]
[TD]43113[/TD]
[TD]43120[/TD]
[TD]43127[/TD]
[TD]43134[/TD]
[TD]43141[/TD]
[TD]43148[/TD]
[TD]43155[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]06 Jan[/TD]
[TD]13 Jan[/TD]
[TD]20 Jan[/TD]
[TD]27 Jan[/TD]
[TD]03 Feb[/TD]
[TD]10 Feb[/TD]
[TD]17 Feb[/TD]
[TD]24 Feb[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Item 1[/TD]
[TD]71088[/TD]
[TD]62959[/TD]
[TD]15248[/TD]
[TD]11246[/TD]
[TD]10224[/TD]
[TD]0[/TD]
[TD]45774[/TD]
[TD]45114[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Item 1[/TD]
[TD]11457[/TD]
[TD]44584[/TD]
[TD]32551[/TD]
[TD]45445[/TD]
[TD]10884[/TD]
[TD]0[/TD]
[TD]54112[/TD]
[TD]45234[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Item 2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]12245[/TD]
[TD]111254[/TD]
[TD]12557[/TD]
[TD]1245[/TD]
[TD]15996[/TD]
[TD]45455[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Item 3[/TD]
[TD]122245[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]11112[/TD]
[TD]45458[/TD]
[TD]77855[/TD]
[TD]84451[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

try this sumproduct() solution

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1		43106	43113	43120	43127	43134	43141	43148	43155
2		06-Jan	13-Jan	20-Jan	27-Jan	03-Feb	10-Feb	17-Feb	24-Feb
3	Item 1	71088	62959	15248	11246	10224	0	45774	45114
4	Item 1	11457	44584	32551	45445	10884	0	54112	45234
5	Item 2	0	0	12245	111254	12557	1245	15996	45455
6	Item 3	122245	0	0	0	11112	45458	77855	84451
7
8
9		17-Feb
10	Item 1	99886
Sheet2

Cell Formulas
Range	Formula
B10	=SUMPRODUCT($B$3:$I$6,(A3:A6=A10)*(B2:I2=B9))

 

[rahulbassi]

Hello,

Please see if pivot option can help.

For data like this:
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Items[/TD]
[TD="width: 64"]Week1[/TD]
[TD="width: 64"]Week2[/TD]
[TD="width: 64"]Week3[/TD]
[/TR]
[TR]
[TD]Item1[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Item1[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]Item2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Item2[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD]Item3[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]32[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]


Pivot can do this:

[TABLE="width: 386"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of Week1[/TD]
[TD]Sum of Week2[/TD]
[TD]Sum of Week3[/TD]
[/TR]
[TR]
[TD]Item1[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]Item2[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]58[/TD]
[/TR]
[TR]
[TD]Item3[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]118[/TD]
[/TR]
</tbody>[/TABLE]
[TR]
[TD="align: right"][/TD]
[/TR]

</tbody>[/TABLE]

 

[GazNicki]

SUMPRODUCT was the way to go. Thank you.

 

[AlanY]

you're welcome