Please refer file attached.
I needed formula in my sheet name "VF05_UTILITY" in column BA - Tax Rates("SGST+CSST+IGST"+0+0) depending on tax rates(column A, B & C) and Dealer Code (sheet: "Dealer Details") to calculate whether the dealer code come in SGST / CGST or IGST.
i'm not sure i unbderstand the requirement given by your example
looks like you just add up the rows as you have a "SGST+CSST+IGST"+0+0 amount for each row
however
row 12 and 13
has the same dealer code and different "SGST+CSST+IGST"
a little confused about what you need
if you change the yes and no to a 0 and 1
you could then use a lookup and multiply by the result
=(INDEX('DEALER DETAILS'!$R$5:$R$17,MATCH(VF05_UTILITY!H3,'DEALER DETAILS'!$A$5:$A$17,0)) * VF05_UTILITY!A3 )+(INDEX('DEALER DETAILS'!$S$5:$S$17,MATCH(VF05_UTILITY!H3,'DEALER DETAILS'!$A$5:$A$17,0)) * VF05_UTILITY!B3 )+(INDEX('DEALER DETAILS'!$T$5:$T$17,MATCH(VF05_UTILITY!H3,'DEALER DETAILS'!$A$5:$A$17,0)) * VF05_UTILITY!C3 )
Last edited by etaf; 12-31-2018 at 07:11 AM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
In BA3 copied down:
=SUMPRODUCT(($A3:$C3)*('DEALER DETAILS'!$A$5:$A$17=$H3)*('DEALER DETAILS'!$N$5:$P$17="YES"))
Last edited by AliGW; 12-31-2018 at 07:06 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
are you saying you need this...
=A3+B3+C3+BE3+BF3+BG3
or if the other IGST then this...
=A3+B3+bd3+BE3+BF3+BG3
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
This is not clear at all.
Are you looking for a numerical calculation in BA or a string that contains the relevant tax rates. In any case your BB:BD columns seem to reflect or at least use the dealer tax rates.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
I'm really sorry that I've unalbe to express exactly what I need in my original post.Originally Posted by AliGW
I didnt need SUM of tax rates but to combine as text in the format as ( SGST TAX RATE"+"CGST TAX RATE"+"IGST TAX RATE"+0+0" ) just like the already available values in range BA3 to BA13 in my attached file.
Last edited by abhinavbinkar; 12-31-2018 at 07:30 AM.
Then please explain exactly what you DO want. As you can see, nobody is yet certain.
When you say combine do you mean concatenate the A3:C3 cell values as strings or you are adding them.
It's confusing since your current 'formula!' doesn't start with an "="
assuming you want the result exactly as shown in the cell , BUT I suspect theres more needed, like Columns BE etc as you said +0+0
slight modification to the formula I posted
=(INDEX('DEALER DETAILS'!$R$5:$R$17,MATCH(VF05_UTILITY!H3,'DEALER DETAILS'!$A$5:$A$17,0)) * VF05_UTILITY!A3 )&"+"&(INDEX('DEALER DETAILS'!$S$5:$S$17,MATCH(VF05_UTILITY!H3,'DEALER DETAILS'!$A$5:$A$17,0)) * VF05_UTILITY!B3 )&"+"&(INDEX('DEALER DETAILS'!$T$5:$T$17,MATCH(VF05_UTILITY!H3,'DEALER DETAILS'!$A$5:$A$17,0)) * VF05_UTILITY!C3 )&"+0+0"
so instead of &"+0+0"
you can add the cells Be3 & Bg3
=(INDEX('DEALER DETAILS'!$R$5:$R$17,MATCH(VF05_UTILITY!H3,'DEALER DETAILS'!$A$5:$A$17,0)) * VF05_UTILITY!A3 )&"+"&(INDEX('DEALER DETAILS'!$S$5:$S$17,MATCH(VF05_UTILITY!H3,'DEALER DETAILS'!$A$5:$A$17,0)) * VF05_UTILITY!B3 )&"+"&(INDEX('DEALER DETAILS'!$T$5:$T$17,MATCH(VF05_UTILITY!H3,'DEALER DETAILS'!$A$5:$A$17,0)) * VF05_UTILITY!C3 )&"+"&VF05_UTILITY!BE3&"+"&VF05_UTILITY!BG3
Last edited by etaf; 12-31-2018 at 07:48 AM.
Exactly what I was looking for. Thanks you so much Sir. SOLVED.
you are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks