VAT Contents of Receipts

[AllisterB]

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
	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)

 

[jasonb75]

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.

In most cases it doesn't need Ctrl Shift Enter but sumproduct is still an array formula.

The vat of March receipts would be =SUMPRODUCT(AB13:AB17,$H13:$H17)

 

[AllisterB]

Thank YOu

However 10% of the $100 = $10 but I think the answer is 1/11 of $100 (9..09).

How do you do this - does one have to use sumproduct?

Thanks

 

[jasonb75]

There are probably many ways but sumproduct would likely be the most efficient with a single formula.

The ideal way would be to have another table in the same format as the existing ones in order to calculate the vat for each individual row / month combination.

I had the earlier formula reversed, that was calculating vat to be added, not deducted. This one should be correct, I'll have another look though to see if there is a more efficient method.

=AB19-SUMPRODUCT(AB13:AB17/(1+$H13:$H17))

 

[AllisterB]

Thanks for that - and for looking for a more efficient method

Thank You

Allister

 

[AllisterB]

Hi I am using this for a different purpose - this time there is text and other formula in the column between the values of VAT Inclusive values.

How do I exclude any rows where the VAT is not a number and/or where the value in another column on each row is not "Data"

Thanks

 

[jasonb75]

Could you post an example sheet of this new layout along with the expected results as you did in post 1 please. Your description is open to interpretation and many wrong answers.

 

[AllisterB]

Hi

The attached shows taht teh formula does not work if there is text in the column being addressed (col AD). is thee a way to overcome this. I was trying to get theSUMPRODUCT to look at teh value in Col AA and only included those with "Sales" Value.

Thanks

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	AG	AH
11				VAT exclusive								VAT Inclusive										Received
12		VAT		Jan	Feb	Mar	Apr	May				Jan	Feb	Mar	Apr	May					Source	Jan	Feb	Mar	Apr	May
13	A	10%		100	200	300		400		1000		110	220	330	0	440		1100			Sales	0	50	100	0	0		150
14	B	0%			300					300		0	300	0	0	0		300			Sales	0	0	0	0	0		0
15																								100
16																								Text
17	C	10%					200			200		0	0	0	220	0		220			Sales	0	0	220	0	0		220
18	D	0%			100					100		0	100	0	0	0		100			Sales	0	0	30	0	0		30
19	E	0%				200				200		0	0	200	0	0		200			Sales	0	80	0	20	0		100
20
21																								450				500
22																								#VALUE!
Sheet1

Cell Formulas
Range	Formula
R13:V14,R17:V19	R13	=ROUND(J13*(1+$H13),0)
P13:P14,AH17:AH19,X17:X19,P17:P19,AH13:AH14,X13:X14	P13	=SUM(J13:N13)
AD15	AD15	=SUM(AD13:AD14)
AH21,AD21	AH21	=SUM(AH13:AH20)
AD22	AD22	=AD21-SUMPRODUCT(AD13:AD19/(1+$H13:$H19))

 

[jasonb75]

See if this does it, needs to be array confirmed with Ctrl Shift Enter.

=AD21-SUMPRODUCT(IFERROR(AD13:AD19/(1+$H13:$H19),0))

 

[ebea]

Should'n your Sum in AD15 be removed? In AD22 you calculate the sum AD13:AD14 twice, by having Sum AD15 in the formula! And if you use Sum for calculation, instead of Sumproduct, you want have problem with the Text value in AD16.