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?

	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

<tbody>
</tbody>

 

[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:

Items Week1 Week2 Week3 Item1 22 4 5 Item1 22 24 Item1 23 Item2 2 43 4 Item2 22 54 Item3 21 34 32 <colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody> </tbody>

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>


Pivot can do this:

Row Labels	Sum of Week1	Sum of Week2	Sum of Week3
Item1	44	28	28
Item2	24	43	58
Item3	21	34	32
Grand Total	89	105	118

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

</tbody>

 

[GazNicki]

SUMPRODUCT was the way to go. Thank you.

 

[AlanY]

you're welcome