Dear awesome viewers! Thank you for your amazing engagement! Remember, the most liked comment gets pinned - so start liking your favorites now! I love your questions and will try to respond to as many as I can. For quick generic queries, the comment section is perfect. But need more in-depth, personalized advice? Book a personalized consultations with me via topmate.io/mehran_vahedi. They're ideal for detailed, specific questions. Your support means the world to me. Let's keep the conversation going! Thank You!
Hi there! I'm thrilled to hear that our video was helpful to you! 😊 If you liked this content and want more useful tips and updates, consider subscribing and turn on notifications. That way, you won't miss out on any of the upcoming videos. Thanks for watching and for your support!
Thank you for your kind words! I'm glad you found the explanation helpful. Stay tuned for more videos where we'll dive even deeper into similar topics and share more insightful content. Make sure to subscribe and hit the notification bell so you won't miss out on any future videos!
Hello, thank you very much for leaving a comment. I understand what you mean. THat would be a combination of the SUMIFS and VLOOKUP function to take into account the date as well and it will be an interesting video. I will create and publish the video by tomorrow evening EST. Make sure you subscribe and turn on notifications so you wont miss out on it. Thanks again!
As promised I just published my latest video on how to use the SUMIFS and VLOOKUP functions to sum based on name and also based on date as you had requested. Check it out and let me know what you think : th-cam.com/video/E18T-m4r38U/w-d-xo.html
Thank you very much for the kind wishes. I am happy that you found the video useful. I will be posting a lot of new videos related to VLOOKUPs and Excel in general so if you haven't already make sure you subscribe and turn on notifications.
Hi @BalaJi-rw4ez, Thank you for your comment and for watching my video! I appreciate your feedback and I'm excited to share that I have just published a new video explaining how to use the SUMIF and VLOOKUP functions across different workbooks, based on your request. You can watch it here: th-cam.com/video/FP-GYGojI6M/w-d-xo.html. Your suggestions are invaluable and help me create content that meets your needs. Please keep watching and feel free to ask any questions or make more recommendations. Happy learning! Best, Mehran
Hello Sir, A B C 1.20 40 30 2.(10) 50 [60 3. 20 30 20 4. 50 10 10 5. 60 10 80 6. 30 (90) 50] 7. 20 20 70 In above eg. A2 has minimum value & B6 has maximum value, so from this range 2-6 I want sum of column C2-C6 (60+20+10+80+50). so from the 1st column minimum value cell to 2nd column maximum value cell, I want sum of in-between cells of column C. I have 1000s sets each set of 7 of 3rows with 3 columns to execute in this manner. I m providing one more eg. so that u have better understanding. A B C 1. 50 30 30 2. 60 20 40 3. (30) 10 [50 4. 90 80 60 5. 70 (90) 30] 6. 50 70 20 7. 40 40 10 from min value of column A3(30) to max value B5(90), sum all cells of column C, C3 to C5 that is [50+60+30]. Please help with this question. Thank You.
Hello Harsinchh, thank you so much for the message and for bringing up this very interesting challenge. I went through it and I was able to find a solution. Here is how to get this done. You need four helper columns for getting this done. Just follow these 6 simple steps and you will get exactly what you want (I am assuming your data set is located in columns A, B and C as you mentioned above): STEP 1] Input the following formula in cell D2 and drag all the way down so it is applied to all 7 rows : =RANK(A2,$A$2:$A$8,1) STEP 2] Input the following formula in cell E2 and drag all the way down so it is applied to all 7 rows : =RANK(B2,B$2:$B$8,0) STEP 3] Input the following formula in cell F2 ONLY : =IF(D2=1,1,D2) STEP 4] Input the following formula in cell F3 and drag all the way down so it is applied to all rows: =IF(AND(OR(F2=1,F2>10),E21),1,D3) STEP 5] Input the following formula in cell G2 and drag all the way down so it is applied to all 7 rows : =IF(F2=1,C2,0) STEP 6] the sum of values in column G (i.e. G2:G8) is the outcome that you are looking for I tested this on multiple sets of 7 in 3 rows and it worked as intended. Please give it a try and share your thoughts. If you are running into any issues please let me know and feel free to post any other questions, comments or suggestions. Cheers, Mehran
@@realmehranvahedi Hello Sir, Thank You for the Solution, it is working. But how to copy or drag the formula for a single click apply to thousands of sets placed in vertical position. Please Reply.
Hello @@harsinchh4342 , I am happy to hear it is working. I understand what you are saying. if the data is repeated in sets of 7 as you stated the solution is to copy paste the formulas in the solution I provided above into the next set of 7 and then copy paste the existing formulas for two sets of 7 rows into the next 2 sets of 7 rows and then you will have formulas generated for 4 sets of 7 rows, then copy them into the next 4 sets of 7 rows. since the number of sets you are copying from is doubling in every step you will be able to apply the formula to the entire range in 8 to 10 repetitions (2^10= 1024). Let me know if you are able to resolve it.
@@realmehranvahedi Hello Sir, I found one formula for this =SUM(INDIRECT(ADDRESS(MATCH(MIN(A1:A7),A1:A7,0),3,4,1)):INDIRECT(ADDRESS(MATCH(MAX(B1:B7),B1:B7,0),3,4,1))) But it seems there is any logic missing from this formula (might be in writing 3 which is column no.) Can you u please check the formula & find the required correction in the above formula. Please Reply Thank You.
![[LIVE] : ONE ลุมพินี 88 | คู่เอก "ป้อมเพชร vs อัสลามจอน"](http://i.ytimg.com/vi/1ZqTVVbMgbo/mqdefault.jpg)
Dear awesome viewers!
Thank you for your amazing engagement! Remember, the most liked comment gets pinned - so start liking your favorites now!
I love your questions and will try to respond to as many as I can. For quick generic queries, the comment section is perfect. But need more in-depth, personalized advice? Book a personalized consultations with me via topmate.io/mehran_vahedi. They're ideal for detailed, specific questions.
Your support means the world to me. Let's keep the conversation going!
Thank You!
Thanks!!!! that's helped a lot
Hi there! I'm thrilled to hear that our video was helpful to you! 😊 If you liked this content and want more useful tips and updates, consider subscribing and turn on notifications. That way, you won't miss out on any of the upcoming videos. Thanks for watching and for your support!
Nice and good explanation
Thank you for your kind words! I'm glad you found the explanation helpful. Stay tuned for more videos where we'll dive even deeper into similar topics and share more insightful content. Make sure to subscribe and hit the notification bell so you won't miss out on any future videos!
Thanks a lot.one more question the same function using according to date wise.pls post the video
Hello, thank you very much for leaving a comment. I understand what you mean. THat would be a combination of the SUMIFS and VLOOKUP function to take into account the date as well and it will be an interesting video. I will create and publish the video by tomorrow evening EST. Make sure you subscribe and turn on notifications so you wont miss out on it. Thanks again!
As promised I just published my latest video on how to use the SUMIFS and VLOOKUP functions to sum based on name and also based on date as you had requested. Check it out and let me know what you think : th-cam.com/video/E18T-m4r38U/w-d-xo.html
God bless you
Thank you very much for the kind wishes. I am happy that you found the video useful. I will be posting a lot of new videos related to VLOOKUPs and Excel in general so if you haven't already make sure you subscribe and turn on notifications.
@@realmehranvahedi sure thing, will do!
How to use this function for 2 different workbooks
Hi @BalaJi-rw4ez,
Thank you for your comment and for watching my video! I appreciate your feedback and I'm excited to share that I have just published a new video explaining how to use the SUMIF and VLOOKUP functions across different workbooks, based on your request. You can watch it here: th-cam.com/video/FP-GYGojI6M/w-d-xo.html.
Your suggestions are invaluable and help me create content that meets your needs. Please keep watching and feel free to ask any questions or make more recommendations. Happy learning!
Best,
Mehran
Hello Sir,
A B C
1.20 40 30
2.(10) 50 [60
3. 20 30 20
4. 50 10 10
5. 60 10 80
6. 30 (90) 50]
7. 20 20 70
In above eg. A2 has minimum value & B6 has maximum value, so from this range
2-6 I want sum of column C2-C6 (60+20+10+80+50).
so from the 1st column minimum value cell to 2nd column maximum value cell, I want sum of in-between cells of column C.
I have 1000s sets each set of 7 of 3rows with 3 columns to execute in this manner.
I m providing one more eg. so that u have better understanding.
A B C
1. 50 30 30
2. 60 20 40
3. (30) 10 [50
4. 90 80 60
5. 70 (90) 30]
6. 50 70 20
7. 40 40 10
from min value of column A3(30) to max value B5(90), sum all cells of column C, C3 to C5 that is [50+60+30].
Please help with this question.
Thank You.
Hello Harsinchh, thank you so much for the message and for bringing up this very interesting challenge. I went through it and I was able to find a solution. Here is how to get this done. You need four helper columns for getting this done. Just follow these 6 simple steps and you will get exactly what you want (I am assuming your data set is located in columns A, B and C as you mentioned above):
STEP 1] Input the following formula in cell D2 and drag all the way down so it is applied to all 7 rows : =RANK(A2,$A$2:$A$8,1)
STEP 2] Input the following formula in cell E2 and drag all the way down so it is applied to all 7 rows : =RANK(B2,B$2:$B$8,0)
STEP 3] Input the following formula in cell F2 ONLY : =IF(D2=1,1,D2)
STEP 4] Input the following formula in cell F3 and drag all the way down so it is applied to all rows: =IF(AND(OR(F2=1,F2>10),E21),1,D3)
STEP 5] Input the following formula in cell G2 and drag all the way down so it is applied to all 7 rows : =IF(F2=1,C2,0)
STEP 6] the sum of values in column G (i.e. G2:G8) is the outcome that you are looking for
I tested this on multiple sets of 7 in 3 rows and it worked as intended. Please give it a try and share your thoughts. If you are running into any issues please let me know and feel free to post any other questions, comments or suggestions. Cheers, Mehran
@@realmehranvahedi Hello Sir,
Thank You for the Solution, it is working.
But how to copy or drag the formula for a single click apply to thousands of sets placed in vertical position.
Please Reply.
Hello @@harsinchh4342 , I am happy to hear it is working. I understand what you are saying. if the data is repeated in sets of 7 as you stated the solution is to copy paste the formulas in the solution I provided above into the next set of 7 and then copy paste the existing formulas for two sets of 7 rows into the next 2 sets of 7 rows and then you will have formulas generated for 4 sets of 7 rows, then copy them into the next 4 sets of 7 rows. since the number of sets you are copying from is doubling in every step you will be able to apply the formula to the entire range in 8 to 10 repetitions (2^10= 1024). Let me know if you are able to resolve it.
@@realmehranvahedi Hello Sir,
I found one formula for this
=SUM(INDIRECT(ADDRESS(MATCH(MIN(A1:A7),A1:A7,0),3,4,1)):INDIRECT(ADDRESS(MATCH(MAX(B1:B7),B1:B7,0),3,4,1)))
But it seems there is any logic missing from this formula (might be in writing 3 which is column no.)
Can you u please check the formula & find the required correction in the above formula.
Please Reply
Thank You.
only returning the first value for me, not doing a sum