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​

 

[vas6566]

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.

I checked the excel sheet, the table contents are in number format only. I think a little further correction may be needed to the furmula

 

[Joe4]

I checked the excel sheet, the table contents are in number format only. I think a little further correction may be needed to the furmula

The format cell does not matter. If the values were entered as text, changing the format of the cells does NOT change that.
Formatting only changes the appearance, not the underlying value or how the values were entered.

I copy/pasted your data into Excel and got the EXACT same #VALUE errors you show.
I then went to each cell in columns C and D and re-entered the values one-by-one, and the #VALUE error went away, and I got the expected value of 940 for column D.

Try it and see for yourself!
You are asking us for help, so please try our suggestions!

 

[vas6566]

The format cell does not matter. If the values were entered as text, changing the format of the cells does NOT change that.
Formatting only changes the appearance, not the underlying value or how the values were entered.

I copy/pasted your data into Excel and got the EXACT same #VALUE errors you show.
I then went to each cell in columns C and D and re-entered the values one-by-one, and the #VALUE error went away, and I got the expected value of 940 for column D.

Try it and see for yourself!
You are asking us for help, so please try our suggestions!

Hi Joe4
I dont know what is wrong in my excel sheet. I tried as you said - re-entered the values in each cell. Still my sheet shows #VALUE. If you can share the excel sheet in which you got exact value for the formula, thanks in lakhs to you

 

[Joe4]

What happens if when you enter the formulas for Tax, instead of using ENTER, you enter them with CTRL+SHIFT+ENTER?

If you are using an old version of Excel, like 2010, I think that may be necessary to enter array formulas.

 

[vas6566]

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​

Hi, finally it worked with your 90+ % assistance and final touch by Joe4. I got result exactly with following formula style.
=SUM((D2:D10<>0)*$C$2:$C$10)
This time instead of just pressing enter key in formula cell, I used CONTROL+SHIFT+ENTER. Now formula cell works fine. Thank you so much

 

[vas6566]

View attachment 114489

What happens if when you enter the formulas for Tax, instead of using ENTER, you enter them with CTRL+SHIFT+ENTER?

If you are using an old version of Excel, like 2010, I think that may be necessary to enter array formulas.

Thank you Joe4
I used CONTROL+SHIFT+ENTER key in formula cell, now the formula works well. I got the result. Thank you so much , the help that you offered is price less. Hats off to your suggestion.

 

[Joe4]

You are welcome!
Glad you got it working now.

Yeah, with the new versions of Excel, you no longer need to use CTRL+SHIFT+ENTER to enter array formulas, but you still do in the older versions.