Suitable formula needed

[vas6566]

Please check the below table. Here I have sum total of code1, code2 and code3. I need a formula that returns total sum of tax of each code. For example, if manufally, code1 total is 9400, at the same time CODE1's total tax is, if manually calculated, 940. I need suitable formula that return total tax ( mentioned in column B ) of CODE1, CODE2 AND CODE3 just below the sum total of each codes. thanks in advance

	A	B	C	D	E
1	AV	TAX	CODE1	CODE2	CODE3
2	1000​	100​	1000​	0​	0​
3	5000​	500​	0​	5000​	0​
4	3000​	300​	0​	0​	1000​
5	7000​	700​	7000​	0​	0​
6	2000​	200​	0​	2000​	0​
7	1500​	150​	0​	0​	1500​
8	1400​	140​	1400​	0​	0​
9	1200​	120​	0​	1200​	0​
10	700​	70​	0​	0​	700​
Total	22800​	2280​	9400​	8200​	3200​

 

[Joe4]

I have to say, I have read through your question a few times, and cannot say it is clear to me what you are after. In cases like this, it is often helpful to:
- Post a sample of what your data looks like to start
- Post a sample of your expected output
- Explain the logic of exactly how to get to your expected output based on the sample data
- Provide any pertinent formulas being used

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. The nice thing about this tool is that in addition to showing us the data, it also show exactly what rows and columns your data is in, as well as any formulas being used in the range. Instructions on using this tool can be found here: XL2BB Add-in.

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[felixstraube]

How about?:

Book1
	A	B	C	D	E	F
1		AV	TAX	CODE1	CODE2	CODE3
2		1000	100	1000	0	0
3		5000	500	0	5000	0
4		3000	300	0	0	1000
5		7000	700	7000	0	0
6		2000	200	0	2000	0
7		1500	150	0	0	1500
8		1400	140	1400	0	0
9		1200	120	0	1200	0
10		700	70	0	0	700
11	Total	22800	2280	9400	8200	3200
12	Tax			940	820	520
Sheet1

Cell Formulas
Range	Formula
B11:F11	B11	=SUM(B2:B10)
D12:F12	D12	=SUM((D2:D10<>0)*$C$2:$C$10)

But I'm not sure if that is what you want. A list of expected results would be helpful.

 

[vas6566]

=SUM((D2:D10<>0)*$C$2:$C$10)

Hi

felixstraube, I pasted the formula in appropriate cell, it returns some error value. If you dont mind , can you send the excel file itself so that it can be so easy to me understand. Thanks for your reply​

 

[Fluff]

You can copy the mini-sheet into a blank using the copy icon

 

[vas6566]

How about?:

Book1
	A	B	C	D	E	F
1		AV	TAX	CODE1	CODE2	CODE3
2		1000	100	1000	0	0
3		5000	500	0	5000	0
4		3000	300	0	0	1000
5		7000	700	7000	0	0
6		2000	200	0	2000	0
7		1500	150	0	0	1500
8		1400	140	1400	0	0
9		1200	120	0	1200	0
10		700	70	0	0	700
11	Total	22800	2280	9400	8200	3200
12	Tax			940	820	520
Sheet1

Cell Formulas
Range	Formula
B11:F11	B11	=SUM(B2:B10)
D12:F12	D12	=SUM((D2:D10<>0)*$C$2:$C$10)

But I'm not sure if that is what you want. A list of expected results would be helpful.

The expected result in row no. 12 correct. But formula when pasted in separate excel sheet shows error value . As seen below

	AV	TAX	CODE1	CODE2	CODE3
	1000​	100​	1000​	0​	0​
	5000​	500​	0​	5000​	0​
	3000​	300​	0​	0​	1000​
	7000​	700​	7000​	0​	0​
	2000​	200​	0​	2000​	0​
	1500​	150​	0​	0​	1500​
	1400​	140​	1400​	0​	0​
	1200​	120​	0​	1200​	0​
	700​	70​	0​	0​	700​
Total	22800​	2280​	9400​	8200​	3200​
Tax			#VALUE!​	#VALUE!​	#VALUE!​

 

[felixstraube]

Can you share the XL2BB? or the file? Or the formula as you have it in the cell with error?

 

[vas6566]

The expected result in row no. 12 correct. But formula when pasted in separate excel sheet shows error value . As seen below

	AV	TAX	CODE1	CODE2	CODE3
	1000​	100​	1000​	0​	0​
	5000​	500​	0​	5000​	0​
	3000​	300​	0​	0​	1000​
	7000​	700​	7000​	0​	0​
	2000​	200​	0​	2000​	0​
	1500​	150​	0​	0​	1500​
	1400​	140​	1400​	0​	0​
	1200​	120​	0​	1200​	0​
	700​	70​	0​	0​	700​
Total	22800​	2280​	9400​	8200​	3200​
Tax			#VALUE!​	#VALUE!​	#VALUE!​

Hi I am tryping to upload the excel sheet, but the the portal accepts only images like jpg formats. If possible can you guide me how to upload excel file with reply.

 

[vas6566]

Can you share the XL2BB? or the file? Or the formula as you have it in the cell with error?

	AV	TAX	CODE1	CODE2	CODE3
	1000​	100​	1000​	0​	0​
	5000​	500​	0​	5000​	0​
	3000​	300​	0​	0​	1000​
	7000​	700​	7000​	0​	0​
	2000​	200​	0​	2000​	0​
	1500​	150​	0​	0​	1500​
	1400​	140​	1400​	0​	0​
	1200​	120​	0​	1200​	0​
	700​	70​	0​	0​	700​
Total	22800​	2280​	9400​	8200​	3200​
Tax			#VALUE!​	#VALUE!​	#VALUE!​
			=SUM((D2:D10<>0)*$C$2:$C$10)	=SUM((E2:E10<>0)*$C$2:$C$10)	=SUM((F2:F10<>0)*$C$2:$C$10)

 

[Joe4]

If you are getting the #VALUE error, that suggests that all or some your numbers are actually entered as text, and not numbers.
Re-enter them, and it should work out.