How to Calculate sales tax properly from a column of items

[Bond00]

I want to be able to get the totals and correct tax from this.
Really I just need a formula for getting the correct tax value.

assume these values are A1 through A12
$2,993.97
$5,492.30
$7,263.92
$4,759.96
$679.96
$1,119.96
$3,914.95
$820.99
$1,183.99
SubTotal: =SUM(A1:A9)
Tax: ???
Total: =A10+A11

 

[Cubist]

You need provide the tax rule.

 

[Bond00]

You need provide the tax rule.

oh yeah, its 6% tax rate

 

[Sanjeev1976]

You can check the below :

Book2
	A
1	$ 2,993.97
2	$ 5,492.30
3	$ 7,263.92
4	$ 4,759.96
5	$ 679.96
6	$ 1,119.96
7	$ 3,914.95
8	$ 820.99
9	$ 1,183.99
10	SubTotal: =$ 28230.00
11	Tax 6%: =$ 1693.80
12	Total: =$ 29923.80
Sheet1

Cell Formulas
Range	Formula
A10	A10	=SUM(A1:A9)
A11	A11	=A10*6%
A12	A12	=A10+A11

 

[Bond00]

Its not quite that simple sadly. Each item is taxed and rounded down or up on a per item basis. So adding them all up and then figuring tax afterwards doesn't give the correct result.
See the difference here:

Book4
	A	B	C
1		Tax	Real Tax
2	$2,993.97	179.6382	179.64
3	$5,492.30	329.538	329.54
4	$7,263.92	435.8352	435.84
5	$4,759.96	285.5976	285.60
6	$679.96	40.7976	40.80
7	$1,119.96	67.1976	67.20
8	$3,914.95	234.897	234.90
9	$820.99	49.2594	49.26
10	$1,183.99	71.0394	71.04
11
12	tax =	$1,693.80	$1,693.82
Sheet1

So the correct tax amount is 1693.82
Is there a formula that can get each values rounded tax value before doing the sum of it?
Every search I've done and seen hasn't done it correctly. Its always just taking the total and x by the tax % which wont always give the right output. Even youtube vids of how to do this are wrong.

 

[Dave Patton]

T202501a.xlsm
	A	B	C
1
2	2,993.97	179.64	179.64
3	5,492.30	329.54	329.54
4	7,263.92	435.84	435.84
5	4,759.96	285.6	285.6
6	679.96	40.8	40.8
7	1,119.96	67.2	67.2
8	3,914.95	234.9	234.9
9	820.99	49.26	49.26
10	1,183.99	71.04	71.04
11	28,230.00	1,693.82	1,693.82
12
1f

Cell Formulas
Range	Formula
C2:C10	C2	=ROUND(+A2:A10*0.06,2)
B2:B10	B2	=ROUND(A2*0.06,2)
A11:C11	A11	=SUM(A2:A10)
Dynamic array formulas.

 

[Bond00]

Nice formula didn't know about the + in front making it make a list like that (I'm used to using older ver's of excel). =ROUND(+A2:A10*0.06,2)
The issue is I don't want to list all the tax values to the side of the $ values. I just need it to give the total result at the bottom as 1 total value like the top post.
Is there a way do just do that?

Edit:
Oh never mind i see this will work. =SUM(ROUND(+A2:A10*0.06,2))
But only if there is no blank spaces in-between the numbers. Is there a way to fix or account for that? Sometimes there will be some blank spaces between the #'s.
Maybe something like if empty space or if cell isnt a # then assume/replace it with 0?

 

[Bond00]

Ok very odd, it wouldn't work before but if I added 0 in all the blank spaces it then worked, and if i went back and deleted those 0's again it still worked. Why would that be?

So if the formula was entered when the spaces were already blank it gave the #VALUE error but adding 0s in blank spaces fixed it, but then deleting them after didn't make it go back to being a #value error again?
It seems to work with blank cells now, not sure why it didn't the first time. I clicked in the blank cell when it had the value error and then clicked out of it and it then worked. So no idea why..

 

[Dave Patton]

I cannot replicate the problem that you cite. I am using Excel 365.

Can you post an example with the forum's tool named XL2BB?

 

[Cubist]

So if the formula was entered when the spaces were already blank it gave the #VALUE error but adding 0s in blank spaces fixed it, but then deleting them after didn't make it go back to being a #value error again?

It's most likely because those blank cells aren't truly blanks. Try =ISBLANK() on "empty" cells and what does it return?