Hi, I am trying to sum some data from another tab by week using start dates and end date in 2 different columns. For some reason the sums are not working so I'm not sure what is wrong with my formulas. It is definately the dates part of the formula that is not working. If I break down the formula, I can sum all the data but as soon as I add the dates in, I get no results. The references to the other tab are correct.
Can someone help with this? Thank you.
| 2f07b95c-c74e-41bc-87ce-a1eec401c389-canada.csv | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Store Name | Start Date | End Date | Sales (incl. taxes) AR | ||
| 2 | Pizza Parlour (VR) | 28-Sep-20 | 4-Oct-20 | $ - | ||
| 3 | Pizza Parlour (VR) | 5-Oct-20 | 11-Oct-20 | $ - | ||
| 4 | Pizza Parlour (VR) | 12-Oct-20 | 18-Oct-20 | $ - | ||
| 5 | Pizza Parlour (VR) | 19-Oct-20 | 25-Oct-20 | $ - | ||
| 6 | Pizza Parlour (VR) | 26-Oct-20 | 1-Nov-20 | $ - | ||
| 7 | Pizza Parlour (VR) | 2-Nov-20 | 8-Nov-20 | $ - | ||
| 8 | Pizza Parlour (VR) | 9-Nov-20 | 15-Nov-20 | $ - | ||
| 9 | Pizza Parlour (VR) | 16-Nov-20 | 22-Nov-20 | $ - | ||
| 10 | Pizza Parlour (VR) | 23-Nov-20 | 29-Nov-20 | $ - | ||
| 11 | Pizza Parlour (VR) | 30-Nov-20 | 6-Dec-20 | $ - | ||
| 12 | Pizza Parlour (VR) | 7-Dec-20 | 13-Dec-20 | $ - | ||
| 13 | Pizza Parlour (VR) | 14-Dec-20 | 20-Dec-20 | $ - | ||
| 14 | Pizza Parlour (VR) | 21-Dec-20 | 27-Dec-20 | $ - | ||
| 15 | Pizza Parlour (VR) | 28-Dec-20 | 3-Jan-21 | $ - | ||
| 16 | Pizza Parlour (VR) | 4-Jan-21 | 10-Jan-21 | $ - | ||
| 17 | Pizza Parlour (VR) | 11-Jan-21 | 17-Jan-21 | $ - | ||
| 18 | Pizza Parlour (VR) | 18-Jan-21 | 24-Jan-21 | $ - | ||
| 19 | Pizza Parlour (VR) | 25-Jan-21 | 31-Jan-21 | $ - | ||
| 20 | Pizza Parlour (VR) | 1-Feb-21 | 7-Feb-21 | $ - | ||
| 21 | Pizza Parlour (VR) | 8-Feb-21 | 14-Feb-21 | $ - | ||
| 22 | Pizza Parlour (VR) | 15-Feb-21 | 21-Feb-21 | $ - | ||
| 23 | Pizza Parlour (VR) | 22-Feb-21 | 28-Feb-21 | $ - | ||
| 24 | Pizza Parlour (VR) | 1-Mar-21 | 7-Mar-21 | $ - | ||
| 25 | Pizza Parlour (VR) | 8-Mar-21 | 14-Mar-21 | $ - | ||
| 26 | Pizza Parlour (VR) | 15-Mar-21 | 21-Mar-21 | $ - | ||
| 27 | Pizza Parlour (VR) | 22-Mar-21 | 28-Mar-21 | $ - | ||
| 28 | Pizza Parlour (VR) | 29-Mar-21 | 4-Apr-21 | $ - | ||
| 29 | Pizza Parlour (VR) | 5-Apr-21 | 11-Apr-21 | $ - | ||
| 30 | Pizza Parlour (VR) | 12-Apr-21 | 18-Apr-21 | $ - | ||
| 31 | Pizza Parlour (VR) | 19-Apr-21 | 25-Apr-21 | $ - | ||
| 32 | Pizza Parlour (VR) | 26-Apr-21 | 2-May-21 | $ - | ||
| 33 | Pizza Parlour (VR) | 3-May-21 | 9-May-21 | $ - | ||
| 34 | Pizza Parlour (VR) | 10-May-21 | 16-May-21 | $ - | ||
| 35 | Pizza Parlour (VR) | 17-May-21 | 23-May-21 | $ - | ||
| 36 | Pizza Parlour (VR) | 24-May-21 | 30-May-21 | $ - | ||
| 37 | Pizza Parlour (VR) | 31-May-21 | 6-Jun-21 | $ - | ||
| 38 | Pizza Parlour (VR) | 7-Jun-21 | 13-Jun-21 | $ - | ||
| 39 | Pizza Parlour (VR) | 14-Jun-21 | 20-Jun-21 | $ - | ||
| 40 | Pizza Parlour (VR) | 21-Jun-21 | 27-Jun-21 | $ - | ||
Summary A&J | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D10:D40,D2:D8 | D2 | =SUMIFS('2f07b95c-c74e-41bc-87ce-a1eec40'!$W:$W,'2f07b95c-c74e-41bc-87ce-a1eec40'!$A:$A,$A2,'2f07b95c-c74e-41bc-87ce-a1eec40'!$E:$E,">=&$B2",'2f07b95c-c74e-41bc-87ce-a1eec40'!$E:$E,"<='Summary A&J'!&$C2") |
| D9 | D9 | =SUMIFS('2f07b95c-c74e-41bc-87ce-a1eec40'!$W:$W,'2f07b95c-c74e-41bc-87ce-a1eec40'!$A:$A,$A9,'2f07b95c-c74e-41bc-87ce-a1eec40'!$E:$E,">=&B9",'2f07b95c-c74e-41bc-87ce-a1eec40'!$E:$E,"<=&'Summary A&J'!C9") |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| '2f07b95c-c74e-41bc-87ce-a1eec40'!_FilterDatabase | ='2f07b95c-c74e-41bc-87ce-a1eec40'!$A$1:$BI$25101 | D2:D40 |
Can someone help with this? Thank you.
