Guest User

Untitled

a guest
Jan 19th, 2019
79
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.73 KB | None | 0 0
  1. =query({'Sheet1'!I2:I,'Sheet1'!L2:L;'Sheet2'!I2:I,'Sheet2'!L2:L},"Select Col1, sum(Col2) group by Col1 label Col1 'Month'")
  2.  
  3. | Month | sum
  4. |
  5. | 2018/12 | £ 35.00
  6. | 2019/01 | £ 155.00
  7. | 2019/02 | £ 60.00
  8. | 2019/03 | £ 210.00
  9. | Grand Total | £ 460.00
  10.  
  11. | Month | sum | Sheet1 | Sheet2
  12. |
  13. | 2018/12 | £ 35.00 | £10 | £25
  14. | 2019/01 | £ 155.00 | £100 | £55
  15. | 2019/02 | £ 60.00 | £30 | £30
  16. | 2019/03 | £ 210.00 | £110 | £100
  17. | Grand Total | £ 460.00
  18.  
  19. ={QUERY({Sheet1!I2:I, Sheet1!L2:L; Sheet2!I2:I, Sheet2!L2:L},
  20. "select Col1, sum(Col2)
  21. where Col1 is not null
  22. group by Col1
  23. label Col1 'Month', sum(Col2)'Sum'", 0),
  24. {"Sheet1"; QUERY(ARRAYFORMULA(IFERROR(VLOOKUP(QUERY(QUERY({Sheet1!I2:I, Sheet1!L2:L},
  25. "select Col1, sum(Col2)
  26. where Col1 is not null
  27. group by Col1", 0),
  28. "select Col1", 0),
  29. {Sheet1!I2:I, Sheet1!L2:L}, 2), )),
  30. "select Col1 where Col1 is not null", 0)},
  31. {"Sheet2"; QUERY(ARRAYFORMULA(IFERROR(VLOOKUP(QUERY(QUERY({Sheet2!I2:I, Sheet2!L2:L},
  32. "select Col1, sum(Col2)
  33. where Col1 is not null
  34. group by Col1", 0),
  35. "select Col1", 0),
  36. {Sheet2!I2:I, Sheet2!L2:L}, 2), )),
  37. "select Col1 where Col1 is not null", 0)}}
Add Comment
Please, Sign In to add comment