Hi Premor, Really thanks a lot , very much cleared and inspired by your mail.
On Thu, Jul 18, 2013 at 7:11 PM, De Premor <d...@premor.net> wrote: > Sorry for my late response, a have to take a rest for a while (^_^) > > > > *=SUM(IFERROR((Sheet1!$A$3:$E$6=$A4)*(Sheet1!$B$3:$F$6),0))** > ** * > The short explanation on my formula is > > 1. Fist we check the range data in sheet1 > > *Sheet1!$A$3:$E$6=$A4 > * > This* *formula will give TRUE / FALSE result if in each cell in given > range = value in cell A4 > As we know, that value of TRUE is 1 and FALSE is 0, wee need this for > next calculation to eliminate unwanted value > > That formula will result like in image bellow, as we see we found TRUE > value in Col 2 Row 3, and Col 3 Row 2, just like we see in Sheet1 > > > 2. As we see on your data table in sheet1, the value that we want was 1 > column right after the keyword that we test in fist part formula, soo to > make it easy to calculate our wanted value we need to move our > calculation range by 1 column to the right > *(Sheet1!$B$3:$F$6) > * > * > > *3.* *After we have temporary result, soo we need to clean up that error by > replacing error with 0 using IFERROR formula > > > > 4. After we got clean value after IFERROR operation, we can SUM it like > we do as usual, but at this time we need to use CSE because that was > array formula > > * **Thats All (^.^)* > > After we know the main concept of that formulas, we can solve your next > question with this formula > * > =SUM(IFERROR((Sheet1!$A$3:$G$6=$A4)*(Sheet1!$C$3:$I$6),0)) => *Red Value > Shifted 2 Column* > ** > *Rgds, > [dp] > > > Pada 18/07/2013 19:27, Smitha S R menulis: > > Hi premor, > > Please help > > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/groups/opt_out.
<<edhhecch.png>>
<<ddbgibee.png>>
<<cjeffbdh.png>>
<<ajdabide.png>>
