VAT Contents of Receipts

AllisterB

Board Regular
Joined
Feb 22, 2019
Messages
120
Office Version
  1. 365
Platform
  1. Windows
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

VAT.xlsx
GHIJKLMNOPQRSTUVWXYZAAABACADAEAF
11VAT exclusiveVAT InclusiveReceived
12VATJanFebMarAprMayJanFebMarAprMayJanFebMarAprMay
13A10%10020030040010001102203300440110005010000150
14B0%3003000300000300000000
15C10%20020000022002200022000220
16D10%100100011000011000300030
17E0%20020000200002000800200100
18
190130350200500
Sheet1
Cell Formulas
RangeFormula
R13:V17R13=ROUND(J13*(1+$H13),0)
P13:P17,AF13:AF17,X13:X17P13=SUM(J13:N13)
Z19:AD19,AF19Z19=SUM(Z13:Z18)
 
That would require a column for each month which I believe is what the OP is trying to avoid.

I agree that the layout is not ideal in regard to what it does and doesn't contain, personally I would say that to avoid the extra columns something like this might work (I've used 5% instead of 0% for the second amount but it works with anything).

Book2
ABC
1
2Item 110%20
3Item 25%30
4Item 310%40
5
6Received inc vat10%60
75%40
8Received exc vat10%54.55
95%38.10
10Vat10%5.45
115%1.90
Sheet14
Cell Formulas
RangeFormula
C6:C7C6=SUMIF($B$2:$B$4,B6,C2:C4)
C8:C9C8=C6/(1+B6)
C10:C11C10=C6-C8
Hopefully the OP can put a kind of clarity on how he actually think it has to be.
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Thanks for your Input - need to retain theSUM in AD15 and would like to have eth solution conditional on col AA being "SALES".

What is the solution ? :)
 
Upvote 0
As @ebea noted earlier, the SUM in AD15 is creating a false result in AD21.

You have a badly planned out sheet which means that no matter how many ifs and buts we add to the formulas, there will always be a risk of errors in the results.

This is the best suggestion (not solution) that I have based on what we've got to work with, I believe that @ebea has a different layout in mind, it is up to you to choose which one you think will work best.
Book2
GHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
11VAT exclusiveVAT InclusiveReceived
12VATJanFebMarAprMayJanFebMarAprMaySourceJanFebMarAprMay
13A10%100200300400100011022033004401100Sales05010000150
14B0%3003000300000300Sales000000
1505010000150
16Text
17C10%2002000002200220Sales0022000220
18D0%1001000100000100Sales00300030
19E0%2002000020000200Sales0800200100
20
21Sales0130350200500
22
23Sales 10%05032000370
24Exc Vat045290.9100336
25Vat04.529.0910034
26
27Sales 0%08030200130
Sheet17
Cell Formulas
RangeFormula
R17:V19,R13:V14R13=ROUND(J13*(1+$H13),0)
AH27,AH23:AH25,AH21,AH17:AH19,X17:X19,P17:P19,AH13:AH15,X13:X14,P13:P14P13=SUM(J13:N13)
AB21:AF21,AB15:AF15AB15=SUMIF($AA$13:$AA14,"Sales",AB$13:AB14)
AB23:AF23AB23=SUMIFS(AB13:AB19,$AA$13:$AA$19,"Sales",$H$13:$H$19,10%)
AB24:AF24AB24=AB23/1.1
AB25:AF25AB25=AB23-AB24
AB27:AF27AB27=SUMIFS(AB13:AB19,$AA$13:$AA$19,"Sales",$H$13:$H$19,0%)
 
Upvote 0
As jasonb75 point out, then out from the layout, which are provided, it's difficult to could give an optimal solution/setup.
And as it's only part of your own original Sheet, it will be guessing to give a solution. Maybe if we did see, the original Setup, it would be easier to provide a better solution.
For me, the best way, would be, to pull the VAT calculation from received amount, fully out to the right position, as shown in example below.

moms-beregning.xlsx
GHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
11VAT exclusiveTotalVAT inclusiveTotalSourceReceivedTotalVAT Received
12VATJanFebMarAprMayJanFebMarAprMayJanFebMarAprMay
13A10,00%100200300400100011022033004401100Sales0501000015013,64
14B0,00%3003000300000300Sales0000000,00
15C10,00%2002000002200220Sales002200022020,00
16D0,00%1001000100000100Sales003000300,00
17E0,00%2002000020000200Sales08002001000,00
18
1910203020400130350,0020033,64
20
21
22
23VAT exclusiveTotalVAT inclusiveTotalSourceReceivedTotalVAT Received
24VATJanFebMarAprMayJanFebMarAprMayJanFebMarAprMay
25A10,00%100200300400100011022033004401100Sales0501000015013,64
26B0,00%3003000300000300Sales0000000,00
27
28Text
29C10,00%2002000002200220Sales002200022020,00
30D0,00%1001000100000100Sales003000300,00
31E0,00%2002000020000200Sales08002001000,00
32
3310203020400130350,0020033,64
34
35
Ark1
Cell Formulas
RangeFormula
R29:V31,R25:V26,R13:V17R13=ROUND(J13*(1+$H13),0)
AH29:AH31,X29:X31,P29:P31,AH25:AH26,X25:X26,P25:P26,AH13:AH17,X13:X17,P13:P17P13=SUM(J13:N13)
AJ29:AJ31,AJ25:AJ26,AJ13:AJ17AJ13=AH13-SUM(AB13:AF13/(1+H13))
R19:V19R19=SUM(R13:R17-J13:J17)
AJ19,AB19:AF19AB19=SUM(AB13:AB17)
R33:V33R33=SUM(R25:R31-J25:J31)
AJ33,AB33:AF33AB33=SUM(AB25:AB31)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AB25:AH31Expression=HVIS($H25:$H31>0;SAND)textNO
AB13:AH17Expression=HVIS($H13:$H17>0;SAND)textNO
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top