Sir ap yaqeen krain maine offset function smajhny k liye bohat video dhaikhi magar kuch smajh nahi aya lakin mjhy yaqeen tha k jab ap sikhao gy mjhy zror smajh ajaye ga aur aj qasam sy mujhy clear smajh aagya ye function. Sir you are great live you long life sir. I am very impressed from your way of talking. Thanks sir
Thanks Zahid Sahab for your trust and love ,Really means a lot to me,Aapke life me Ye Tutorial value add kar pa Raha hai aur aap seekh rhe hain mere liye ye bbt badi baat h🙏❤
U r really amazing sir, ur teaching concept is very easy to make me understand vry fast thank u sir for making a video for us. Ur video really inspired me to watch & learn more & more.
Thanks Sanjana ,Apke ye words mere liye bahut mayne rakhta hai dear.Meri humesha koshish rhegi Kuch best content deliver krne ki..Seekhiye rhiye aur life me aage badhte rhiye ..❤️👍👍👍
I am watching your some video apne itna ache se smjhya ki ab hr video dekhni hogi muje. God Level Excel Seriously sir. I am using offset with match only but is video me dekha ki iska or b way se use ho skta h.😍😍😍😍
@@Innozant yes sir, pr mujhe khi v structured way me Excel video nhi mil pa rha tha.. isliye regular nhi tha.. pr ab main aapke channel ko regular follow kr rha hu ...dil se aapka dhanyawad sir.
Haan Adarsh Ji yahi sochke Maine channel bhi start kiya ki wo student Jo fees afford nhi kr skte h ya aur issue unhe is platform ke madhyam se Complete course tutorial mil paye🙏❤️
Seriously combination is awesome offset and counta A Only i know offset (in Macros writing) but now i learned in other form also. Thank you Sir Pls share contact details of your if possible because i want to query about SQL and Access
Amazing. Please advise if I am correct. what ever we subtract from counta, is actually the height(of range we extract) in case of sum the rows and is actually the width(of range we extract) in case of sum the columns.
Sir, I have a query regarding date format questions Like we have given unequal date format and we have to make it in one format eg.(12/3/1994,12-3-1994,12-feb-1994,12.3.1994), If i am using ctrl1 and selecting custom option ,every date format is changed as per require format but (12.3.1994) will not change. Plz tell me the reason behind this .
Can anyone solve my problem i.e. ek sheet-1 (result wali) me dusri sheet-2 (adjacent) se values uthani hai pr duplicate Lookup values hain Kyki sheet-1 me columns me agle wale group ka data Lana hai (jo next duplicate Lookup value ka hoga) mene indirect fxn lgaya pr 3rd duplicate Jo sheet-1 ke 3 column me Lani hai wo nhi aa rhi ... kya offset fxn kam krega ya (note: sheet-1 pe lookup value ke samne 5 columns me next duplicate Lookup ki value le kr ani hai) I can send you Excel sheet to look for real problem
sir ye offset function me agr hum match function aur use kre to ye aur dynamic bn skta h for example mene c21 and c22 me month and directions ke respectively list bnayi data validation se and offset formula ke row and column ke reference me match function use kiya like this : =OFFSET(A12,MATCH(C22,A13:A18,0),MATCH(C21,B12:H12,0)) isse data validation ki list se jo month and jo direction(east west....) select kroge vo answer milega
and sir apke question ka aur dynamic method se answer ye rha for sum of last 3 rows: =sum(offset(A12,COUNTA(A13:A20),MATCH(C21,B12:H12,0),-3,1)) for sum of last 4 column: =SUM(OFFSET(A12,MATCH(C22,A13:A18,0),COUNTA(B12:J12),1,-4)) again c21 is for months and c22 is for direction(south, east, west ........)
Nice video I have one question Suppose row contain, roll number,name and attendance Attendance start from 1to 30, But some row start attendance from 8,12,15 After that he left some blank cell, I want to replace blank with A ,how can I get cells are blank after date 8,12,15 and replace A. I have 200 rows
Hahaha..You are Right Sunil..Thumbnail dekhke hi Laura hai..Jaado tona start hone walk hai.baad me practice challah hai video ke and are like kar do aur subscribe kar do😜😜😜
Sir, there is a doubt after watching this video so many times. Agar hum offset with CountA use krte hain to apne jo formula bataya vo syntax ko support nhi krta. Sytnax is =offset(reference, rows, columns,height,width). If we use it with CountA to hum reference ke baad D column par kaise pahuche. Or agar D column par pahuche to columns k lye jo 3 pass kiya hai to fir vo kis lye coz column par to hum pehle hi pahuch chuke hain. Sir I follow your channel from long time and try to learn excel from your videos. This point is not clear. Please reply sir Regards
Sir meine bill gates ka ek interview pada tha jaha unhone likha "If we stop employing foreign engineers especially from India. Then there will be another Microsoft born in India" And apka chennal dekh liya bill gates ji ne sayad un heartattack ho gayga😅😅😅
I am regular student of ur online class....
Today i can say that You are GOD OF EXCEL
Omg...rulaoge kya ab aap😊❤️
@@Innozantó⁸
Best Teacher around the world.
Aisi learning and Teacher paise dekar v kavi nhi milnewala kisi ko
Thanks Manish ji for valuable appreciation ❤️
Sir ap yaqeen krain maine offset function smajhny k liye bohat video dhaikhi magar kuch smajh nahi aya lakin mjhy yaqeen tha k jab ap sikhao gy mjhy zror smajh ajaye ga aur aj qasam sy mujhy clear smajh aagya ye function. Sir you are great live you long life sir. I am very impressed from your way of talking. Thanks sir
Thanks Zahid Sahab for your trust and love ,Really means a lot to me,Aapke life me Ye Tutorial value add kar pa Raha hai aur aap seekh rhe hain mere liye ye bbt badi baat h🙏❤
Allah apko kamyabi ki bulandi tak leker jaye Ameen, kamal ka trick bataya ap nay infact ap excel maharaj hai.
Thanks dear♥️♥️👍
Super Super Excellent you are a very sincere Godly Person
Thanks dear.Keep learning 👍❤️
Your way of teaching is super sir
Thanks Kiran...Happy Learning dear 👍❤️🙏
U r really amazing sir, ur teaching concept is very easy to make me understand vry fast thank u sir for making a video for us. Ur video really inspired me to watch & learn more & more.
Thanks Shweta 👍happy learning
As usual best teaching
Thanks Manish Ji ❤️👍
Your teaching skills are very nice and understand able .
Thanks Sanjana ,Apke ye words mere liye bahut mayne rakhta hai dear.Meri humesha koshish rhegi Kuch best content deliver krne ki..Seekhiye rhiye aur life me aage badhte rhiye ..❤️👍👍👍
I am watching your some video apne itna ache se smjhya ki ab hr video dekhni hogi muje. God Level Excel Seriously sir. I am using offset with match only but is video me dekha ki iska or b way se use ho skta h.😍😍😍😍
Concept clear sir..... i like your teaching skills.
=SUM(OFFSET(I8,3,COUNTA(I11:P11)-3,1,3))
👍❤️
Sir you are best teacher for me and thank you sir you have good style teaching 🙏🙏
Thanks Rahul ji..Really means a lot to mw
Aapka explain karne ka tarika mujhe bahut accha laga
Khoob Bhalo Video, Sir Ji🙏.
Thanks ❤️😀🙏
Sir become your fan,🙏🙏🙏🙏 it's very easy way to teaching tough topic...God bless you 🙏🙏🙏🙏
Thanks Dear Ashish ji..❤❤🙏🙏Happy learning dear.
The best teacher
Thanks Bharat❤️
I love it bro, your style of explanation is unique 👌
Thanks
Very good explanation. You are really an excellent teacher.
Very helpful video for students (beginners)
I was waiting for this function. Thank you.
Ok..seekhiye step by step
Awesome way sir, No Words for this.
Thanks Arijeet ji❤️
Thank you so much Sir. So nice of you.
Thanks Binay ji
Sir your way of expressing is osm
Thanks ❤️
really smart teaching!!!
Thanks .Keep learning
Sir je jabardast aapk ko Salute hain
Thanks Amit ji.. Happy Learning 🙏♥️
Kamal hai sir apke videos...
❤️👍
Concept clear sir..... i like your teaching skills...😍😍😍❤❤❤
Thanks Jitender ji❤️🙏GN.. Really means a lot to me
@@Innozant thats why, i always like your videos... Good night sir..😍😍
You know magic, Sir.
Thanks Indrani ji.Happy learning ❤️
Naya hatke .and so much needful..
Ek bat sir.jatadhari ji kab aayenge.i am waiting 😀😀❤️❤️
Haha..itna Pasand aa gye Jatadhari Ji aapko
Sir tussi great ho 🖐️
Ur learning style is owsome🙏🙏👍👍 thanks a lot
Thanks Abhishek Ji🙏❤️Seekhiye aur life m aage badhiye.. Learning humesha krte rahen👍
Great explanation
Truly exllent sir
You are great sir
Wow... thanks dear🙏♥️👍
Great Interpretation
Thanks Rajdeep for your valuable comment dear 🙏♥️
Great bro ...TQ so much ❤️
Thank you
Very nice functions offset and counta combination for dynamic use in database tricks like sum function thank you very much sir I await
Thanks Pramod Ji..for Supporrting 👍❤️🙏
ans of my excel sheet 272=(=SUM(OFFSET(B3,4,COUNTA(B7:I7)-3,1,3))) thank you sir.....
very well explained! Thanks.
Thanks Saurabh Ji❤️🙏
Kamal..kamal andaz
Thanks Saira.Happy learning dear..
very superb sir
I understand offset thanks sir 😇😇
Thanks Satish Ji..GN♥️🙏
Thanks a lot, sir ji.
❤️🙏
Superb example 🎉🎉
Thanks Saddam Sahab 🙏♥️👍
Finally done 😁😁😁😁 after more and more practice 😊☺️😊☺️
Gajab kar diya..dekha maine bola tha na us din khud se try kriye kaise nhi hoga..❤❤good job Sweta
@@Innozant yss sir👍👍
Thank you for your effort sir ♥️
Thanks Adarsh Ji..Umeed hai aap Regular Naye videoa ke Saath hi purane topic Ka Practice bhi Kar rhe honge
@@Innozant yes sir, pr mujhe khi v structured way me Excel video nhi mil pa rha tha.. isliye regular nhi tha.. pr ab main aapke channel ko regular follow kr rha hu ...dil se aapka dhanyawad sir.
Haan Adarsh Ji yahi sochke Maine channel bhi start kiya ki wo student Jo fees afford nhi kr skte h ya aur issue unhe is platform ke madhyam se Complete course tutorial mil paye🙏❤️
U r impeccable
Thanks Sir!
Dhanyavaad sir ❤
Thanks Ravi Ji
Great sir ...
Thanks Bhoopendra ji
Very nice.
Thank you
Superb 🙏
Thanks dear❤️🙏
Very informative. English trainers are making fool on various online learning platform.👍👍👍👍
Thanks Kailash ji,Really means a lot to me..Happy learning..God bless you dear
Shi me sir bhagwaan ho aap Excel...
Amazing sir
Thanks Zoie Raem.. Happy Learning dear..❤️🙏GN
How to dynamically sum the columns in next blank column, Please teach
Good evening sir
Super sir
when you select the column what key use to appear $ sign
F4
good 👍🏻👍🏻👍🏻👍🏻👍🏻🔥
Thanks Vijay ji❤❤
Seriously combination is awesome offset and counta A
Only i know offset (in Macros writing) but now i learned in other form also.
Thank you Sir
Pls share contact details of your if possible because i want to query about SQL and Access
Thanks Shivkesh ji.. Happy Learning ❤️🙏You mag share your query on telegram id -@innozantofficial
@@Innozant Query means i want to join SQL and Access Course
I want to know, do you have any plan for SQL and ACCES course ?
Live Classroom Training is Running for Sql ,You may join ,For more INFO Call/Whatsapp-7065069985
Please upload a video on "how to dynamically set print area with offset function in Excel"
Answer of sum of last 4 column of South East is (230)= sum(offset (B3,5,CountA(B8:I8)-4,1,4)).
Bilkul Sahi Sanjana..Ab ready rhiye Offset ko match ke Saath combine krke example Saath hi Advanced Offset ke liye❤️👍ok
Sir, What If i select a range in reference? If choosen then how will I count col & rows?
Amazing.
Please advise if I am correct. what ever we subtract from counta, is actually the height(of range we extract) in case of sum the rows and is actually the width(of range we extract) in case of sum the columns.
Sir i always confused with offset function the way you explain I think I will never forget this function forever.
Sir, I have a query regarding date format questions Like we have given unequal date format and we have to make it in one format eg.(12/3/1994,12-3-1994,12-feb-1994,12.3.1994), If i am using ctrl1 and selecting custom option ,every date format is changed as per require format but (12.3.1994) will not change. Plz tell me the reason behind this .
Use text to coloumn
Classic ecplanation
Thanks Salil ji...Happ learning
Nice sir
🙏🙏
Sir counta place pr hm toh...simple count use kr sakte hai...
👍❤️
Lovely
Thanks ❤️❤️
Thnq sir
Sir samjh mei aa gaya column.... Column jo hai wo width wala column hai na ki na ki reference wala...
❤️👍
Sir Row aur Column Manually Hi Count Karna Padega Kya bade data mai ?
Na na...abhi to ye concept seekh rahe Hain dear ...Abhi bht Kuch banki hai offset me...♥️🙏HV patience
Can anyone solve my problem i.e. ek sheet-1 (result wali) me dusri sheet-2 (adjacent) se values uthani hai pr duplicate Lookup values hain Kyki sheet-1 me columns me agle wale group ka data Lana hai (jo next duplicate Lookup value ka hoga) mene indirect fxn lgaya pr 3rd duplicate Jo sheet-1 ke 3 column me Lani hai wo nhi aa rhi ... kya offset fxn kam krega ya (note: sheet-1 pe lookup value ke samne 5 columns me next duplicate Lookup ki value le kr ani hai) I can send you Excel sheet to look for real problem
sir ye offset function me agr hum match function aur use kre to ye aur dynamic bn skta h
for example mene c21 and c22 me month and directions ke respectively list bnayi data validation se
and offset formula ke row and column ke reference me match function use kiya
like this :
=OFFSET(A12,MATCH(C22,A13:A18,0),MATCH(C21,B12:H12,0))
isse data validation ki list se jo month and jo direction(east west....) select kroge vo answer milega
and sir apke question ka aur dynamic method se answer ye rha
for sum of last 3 rows:
=sum(offset(A12,COUNTA(A13:A20),MATCH(C21,B12:H12,0),-3,1))
for sum of last 4 column:
=SUM(OFFSET(A12,MATCH(C22,A13:A18,0),COUNTA(B12:J12),1,-4))
again c21 is for months and c22 is for direction(south, east, west ........)
Wow..Very Nice Ishu..Next Video offset with Match Ka hi different perspective aayega..Achi baat hai..Good Initiative Ishu🙏♥️
Very good..M impressed 👍♥️100 percent aapko samajh Aaya hai offset formula..Ab complex offset ke liye ready haina?
@@Innozant thank you sir 🙏😊
@@Innozant yes sir,
im ready for complex offset😊🙏
Nice video
I have one question
Suppose row contain, roll number,name and attendance
Attendance start from 1to 30,
But some row start attendance from 8,12,15
After that he left some blank cell, I want to replace blank with A ,how can I get cells are blank after date 8,12,15 and replace A.
I have 200 rows
Aise Ques se better aap sheet share Kar dijiye query apni ..#innozantofficial telegram id
Sir FMS or PMS pr video bnaye interview me bahut pucha jata h
Haan FMS video bhi layenge pehle main conceptual topic finish ho jaye
Sir hight mei 3 row toh samjh mei aa gaya but width mei single column nhi aaya ... Ye pta hai ki hight row ki hoti hai width column ki ...
Very nice sir jee.
सर इसमे counta मेसे 3 ही क्यों माईनस किया
गया। क्या यह भी डायनेमिक हो सकता हैं।
Phirse dekhiye video...Apne dhyan se nhi Dekha..again repeat it..kyu minus Kiya hai 3 ..kyunki.55 par pahuchne ke liye..
@@Innozant yes sir.
क्योंकि हमें 55 पर या लास्ट थ्री पर पहुचना था।
Thanks
bhai excel 2007 mei bhi possible hai ye? mujhse nahi ho raha
Dynamic kyu nhi hoga..dear
Upset Nahi Only Offset 🔥🔥🔥
Thanks Pawan..♥️🙏
Bhai, practise sheet ka link chal nahi raha...
It's working...aap master link ko access kriye
सर youtube पे बहुत से एक्सेल सिखाने वाले चैनल TITTLE और THUMBNAIL येसे लिखते है जैसे एक्सेल नही जादू टोना सिखा रहे है ...😀.
Hahaha..You are Right Sunil..Thumbnail dekhke hi Laura hai..Jaado tona start hone walk hai.baad me practice challah hai video ke and are like kar do aur subscribe kar do😜😜😜
@@Innozant Han sir 😀
Sir -3 is not clear to me.
how, why and when we will minus?
Oho... - 3 isliye kiya kyunki wo 3 value himrn nhi chaiye na id name. Aur ek aurbcell ka value ydi use count kr lega to apna output galat aayega na
Hi Sir, I have a question here can I?
Sure Dear
@@Innozant Sir can u provide me your email id so that I can share my data set
Sir, there is a doubt after watching this video so many times.
Agar hum offset with CountA use krte hain to apne jo formula bataya vo syntax ko support nhi krta.
Sytnax is =offset(reference, rows, columns,height,width). If we use it with CountA to hum reference ke baad D column par kaise pahuche. Or agar D column par pahuche to columns k lye jo 3 pass kiya hai to fir vo kis lye coz column par to hum pehle hi pahuch chuke hain. Sir I follow your channel from long time and try to learn excel from your videos. This point is not clear. Please reply sir
Regards
Why Yugal,Kindly offset concept ke concept ko ek baar phir se dekhiye ..cell reference concey claear hoga aapka
COUNTA to be erplaced with Col
Sir aap to excel ka sagar ho jitna nikalo khatam he nahi hota rose kuch naya aa jata hai Hahaha 😂😊👌🏻
Omg...my pleasure Puran...Ab rulao kya 😭❤️😀❤️
@@Innozant sir innozant vo tufaan hai jo ab majil par ja k he rukega or aap ka gyaan to infinite 🙏 hai
Love You dear ❤️🙏 koshish yhi rhega
Nic sir,
Sir i have need your's help
Plz mnu ae dass sakde o ki basic excel ch ki ki aa jaanda a sir
Basic Excel Playlist video dear..kindly watch it.uske baad advance me sorting filter data validation vlookup Pivote table ye sab Hain♥️👍
Sir mai try kr rii hu niii ho raaa countA ka use se no value # aa raa h🙄🙄
Galat Kar rhi hai Sweta😀Try again..dekhna output apke samne ❤💖
Trend set Karna shikh
Sir meine bill gates ka ek interview pada tha jaha unhone likha "If we stop employing foreign engineers especially from India. Then there will be another Microsoft born in India"
And apka chennal dekh liya bill gates ji ne sayad un heartattack ho gayga😅😅😅
can any one tell how to freeze
Using F4
First view i am
My pleasure dear 🙏♥️
Sir
Offset ne bilkul upset nhi Kiya
⭐⭐⭐⭐⭐ sir plz give my certificate
Already sent ..kindly check your mail
Dynamic bole to kya h
Dynamic bole to😀Dynamic hai...mtlb static data unchanged rehta hai aur dynamic runtime par update .ya ye kahen ki auto update
@@Innozant ohh ok