Hi, we are trying to add together figures (column I in the table) for Year-To-Date and I'm stumped (easy to do!).
We have managed to add together the figures (with criteria where it IS to be counted as per Column H) on the table by Year & Quarter (Column J ) & just by Year (Column K).
But can't figure out how to add them by Year to Date
(By the way, the way they want them for Year To Date is Q1, then Q1&Q2, then Q1&Q2&Q3 etc)
Hope the table makes this a little bit clearer....
We have managed to add together the figures (with criteria where it IS to be counted as per Column H) on the table by Year & Quarter (Column J ) & just by Year (Column K).
But can't figure out how to add them by Year to Date
(By the way, the way they want them for Year To Date is Q1, then Q1&Q2, then Q1&Q2&Q3 etc)
Hope the table makes this a little bit clearer....
| sum-by-year-to-date-with-criteria-01-question.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | Date | Month | Quarter | Year | Year / Qr | Quarter | YTD QR reporting | To Be Counted | Amt | Subtotal Per Year & Quarter | Subtotal Per Year | Subtotal Per Year to Date (going in quarters) | Expected Figures | ||
| 2 | 13/02/2024 | 2 | 1 | 2024 | 2024 / QR 1 | Q1 | Q1 | Yes | 1,235 | 2,035 | 3,610 | 2,035 | |||
| 3 | 15/03/2024 | 3 | 1 | 2024 | 2024 / QR 1 | Q1 | Q1 | Yes | 800 | 2,035 | 3,610 | 2,035 | |||
| 4 | 13/07/2024 | 7 | 3 | 2024 | 2024 / QR 3 | Q3 | Q1-Q2-Q3 | Yes | 975 | 1,575 | 3,610 | 3,610 | |||
| 5 | 13/08/2024 | 8 | 3 | 2024 | 2024 / QR 3 | Q3 | Q1-Q2-Q3 | Yes | 600 | 1,575 | 3,610 | 3,610 | |||
| 6 | 15/06/2024 | 6 | 2 | 2024 | 2024 / QR 2 | Q2 | Q1-Q2 | No | 550 | 0 | 3,610 | 2,035 | |||
| 7 | 28/03/2011 | 3 | 1 | 2011 | 2011 / QR 1 | Q1 | Q1 | No | 1,900 | 0 | 0 | 0 | |||
| 8 | 11/05/2012 | 5 | 2 | 2012 | 2012 / QR 2 | Q2 | Q1-Q2 | Yes | 2,500 | 2,500 | 3,650 | 2,500 | |||
| 9 | 31/10/2012 | 10 | 4 | 2012 | 2012 / QR 4 | Q4 | Q1-Q2-Q3-Q4 | Yes | 1,150 | 1,150 | 3,650 | 3,650 | |||
| 10 | 21/05/2013 | 5 | 2 | 2013 | 2013 / QR 2 | Q2 | Q1-Q2 | Yes | 750 | 750 | 750 | 750 | |||
| 11 | 15/03/2013 | 3 | 1 | 2013 | 2013 / QR 1 | Q1 | Q1 | No | 625 | 0 | 750 | 0 | |||
| 12 | 05/03/2014 | 3 | 1 | 2014 | 2014 / QR 1 | Q1 | Q1 | Yes | 2,000 | 4,100 | 6,900 | 4,100 | |||
| 13 | 25/02/2014 | 2 | 1 | 2014 | 2014 / QR 1 | Q1 | Q1 | Yes | 2,100 | 4,100 | 6,900 | 4,100 | |||
| 14 | 04/08/2014 | 8 | 3 | 2014 | 2014 / QR 3 | Q3 | Q1-Q2-Q3 | Yes | 2,800 | 2,800 | 6,900 | 6,900 | |||
| 15 | 19/03/2015 | 3 | 1 | 2015 | 2015 / QR 1 | Q1 | Q1 | No | 3,000 | 0 | 0 | 0 | |||
Sheet1 | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B15 | B2 | =MONTH(A2) |
| C2:C15 | C2 | =ROUNDUP(MONTH(A2)/3,0) |
| D2:D15 | D2 | =YEAR(A2) |
| E2:E15 | E2 | =CONCATENATE(D2," / QR ",C2) |
| F2:F15 | F2 | ="Q"&C2 |
| G2:G15 | G2 | =IF(C2<2,"Q1",IF(C2<3,"Q1-Q2",IF(C2<4,"Q1-Q2-Q3",IF(C2<5,"Q1-Q2-Q3-Q4","error")))) |
| J2:J15 | J2 | =SUMIFS(I:I,D:D,D2,C:C,C2,H:H,"Yes") |
| K2:K15 | K2 | =SUMIFS(I:I,D:D,D2,H:H,"Yes") |
| M2,M12:M13,M8,M10 | M2 | =+J2 |
| M3 | M3 | =+J2 |
| M4 | M4 | =+J4+J2 |
| M5 | M5 | =+J4+J2 |
| M6 | M6 | =+J2 |
| M9 | M9 | =+J8+J9 |
| M14 | M14 | =+J14+J13 |
