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]
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]