formula for deriving taxable amount from total amount.

[rajdh75]

hi,

can anyone help on this ?

I have a invoice with GST rates of 5%, 12%, 18%, 28% columns.
I want the sum of taxable value i.e. amount before GST.
5% amount before GST in G26 cell,
12% amount before GST in G27 cell,
18% amount before GST in G28 cell,
28% amount before GST in G29 cell,


thanks in advance.

[Pepe Le Mokko]

Please add a sample sheet as described in the yellow banner. thx

[rajdh75]

Hello,
I am attaching the excel file for reference.

The taxable value of 5% should be shown in cell G26 from Grand Total column.
The taxable value of 12% should be shown in cell G27 from Grand Total column.
The taxable value of 18% should be shown in cell G28 from Grand Total column.
The taxable value of 28% should be shown in cell G29 from Grand Total column.
for ex. 5% GST Taxable amount should be 200-9.52= 190.48 & not 200.00.

thanks.

[bob33]

I'm not sure I understood your question; however, I did try to do so in attached.

[boopathiraja]

Hi,

Since you have the base rate then taxable value = BASE RATE * QTY in G26

Please Login or Register  to view this content.

& drag it down



Or other way to derive taxable value from total value then in G26=

Please Login or Register  to view this content.

& drag it down

[rajdh75]

Hello,
I have seen your attachment.
This is not I want.

suppose I have different items having 5%, 12%, 18%, 28% GST rates.

for ex.- as in sheet -
The taxable amount of 5% should be shown in cell G26 from Grand Total column - GST amt.
i.e. 1st & 5th items total-GST amt. = 100+2500=2600-(4.76+119.05)=2476.19

The taxable amount of 12% should be shown in cell G27 from Grand Total column - GST amt.
i.e. 2nd & 6th items total -GST amt. = 400+4032=4432-(42.86+432)=3957.14

The taxable amount of 18% should be shown in cell G28 from Grand Total column - GST amt.
i.e. 3rd, 7th, 9th items total -GST amt. 900+5782+9558=16240-(137.29+882+1458)=13762.71

The taxable amount of 28% should be shown in cell G29 from Grand Total column - GST amt.
i.e. 4th, 8th, 10th items total -GST amt. =1600+8192+1280=11072-(2422)=8650.00

hence,
G26 should show 2476.19
G27 should show 3957.14
G28 should show 13762.71
G29 should show 8650.00

I want sum of 5% taxable amount in G26, sum of 12% taxable amount in G27, sum of 18% taxable amount in G27,
sum of 28% taxable amount in G29.

From taxable amount I am calculating CGST and SGST amt. for their respective columns.

I think sumif function will work, but it won't take (grand total - GST amount) criteria.

attaching updated sheet for reference.

thanks.

[AliGW]

I'm not sure I understood your question; however, I did try to do so in attached.

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

[bob33]

I think attached works. I combined sumifs for the grand total and subtracted the appropriate tax sums.

[hrlngrv]

If you just want to summarize the amounts from M12:M21 in G26:G29, try G26: =SUMIFS($M$12:$M$21,$L$12:$L$21,F26) , then fill G26 down into G27:G29.

[rajdh75]

Hello,
bob33,

Thanks for your solution.
it help to solve my problem.

[bob33]

Good; please indicate the post is solved.