The attached sheet shows various items that are sold - some are subject to 10% VAT others 0% VAT.
The sales value is received in the third table.
I need a formula that will tell me the VAT content of the receipts for each of the months. i.e. how much VAT is in the $350 received in March.
I would prefer not use an array formula due to the large number of formulae that will be needed.
I have been trying to use SUMPRODUCT but haven't been able to crack it.
Thank You.
Allister
The sales value is received in the third table.
I need a formula that will tell me the VAT content of the receipts for each of the months. i.e. how much VAT is in the $350 received in March.
I would prefer not use an array formula due to the large number of formulae that will be needed.
I have been trying to use SUMPRODUCT but haven't been able to crack it.
Thank You.
Allister
VAT.xlsx | ||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | |||
11 | VAT exclusive | VAT Inclusive | Received | |||||||||||||||||||||||||
12 | VAT | Jan | Feb | Mar | Apr | May | Jan | Feb | Mar | Apr | May | Jan | Feb | Mar | Apr | May | ||||||||||||
13 | A | 10% | 100 | 200 | 300 | 400 | 1000 | 110 | 220 | 330 | 0 | 440 | 1100 | 0 | 50 | 100 | 0 | 0 | 150 | |||||||||
14 | B | 0% | 300 | 300 | 0 | 300 | 0 | 0 | 0 | 300 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||||||
15 | C | 10% | 200 | 200 | 0 | 0 | 0 | 220 | 0 | 220 | 0 | 0 | 220 | 0 | 0 | 220 | ||||||||||||
16 | D | 10% | 100 | 100 | 0 | 110 | 0 | 0 | 0 | 110 | 0 | 0 | 30 | 0 | 0 | 30 | ||||||||||||
17 | E | 0% | 200 | 200 | 0 | 0 | 200 | 0 | 0 | 200 | 0 | 80 | 0 | 20 | 0 | 100 | ||||||||||||
18 | ||||||||||||||||||||||||||||
19 | 0 | 130 | 350 | 20 | 0 | 500 | ||||||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
R13:V17 | R13 | =ROUND(J13*(1+$H13),0) |
P13:P17,AF13:AF17,X13:X17 | P13 | =SUM(J13:N13) |
Z19:AD19,AF19 | Z19 | =SUM(Z13:Z18) |