sum formula

[waqas urdu]

excel ma asy no ka kasy sum karo string ka 7 hy inka sum kasy ho ga konsa formula lagy ga

[Asham Khan]

excel ma asy no ka kasy sum karo string ka 7 hy inka sum kasy ho ga konsa formula lagy ga

Aap Ka Sawal Smjh Nahi Aya
Apny Sawal Ki Thori Si Wazahat Kr Den,

[waqas urdu]

d columns ma string ka saat jo number hy unko sum kasay karna hy =sum() ya wala formula tu lagay ga nahee string chor kar number ka sum karna hy

[syar]

d columns ma string ka saat jo number hy unko sum kasay karna hy =sum() ya wala formula tu lagay ga nahee string chor kar number ka sum karna hy

Jahan par aap ko apna matlooba result darkar ha us cell m below Array Formula Enter karain.

=SUMPRODUCT(LEFT(D5:D10,MMULT(LEN(D5:D10)-LEN(SUBSTITUTE(D5:D10,{0,1,2,3,4,5,6,7,8,9},"")),{1;1;1;1;1;1;1;1;1;1}))+0)

Note: Formula must be entered with the combination of CTRL+SHIFT+ENTER not just enter


regards

[waqaskhan722]

kia porblem solved hoi

[waqas urdu]

Jahan par aap ko apna matlooba result darkar ha us cell m below Array Formula Enter karain.

=SUMPRODUCT(LEFT(D5:D10,MMULT(LEN(D5:D10)-LEN(SUBSTITUTE(D5:D10,{0,1,2,3,4,5,6,7,8,9},"")),{1;1;1;1;1;1;1;1;1;1}))+0)

Note: Formula must be entered with the combination of CTRL+SHIFT+ENTER not just enter


regards

thanks
app ya formula explain kar sakty hy

[syar]

thanks
app ya formula explain kar sakty hy

M explanation m bohat acha tu nahee hon lekan try karta hon.
=SUMPRODUCT(LEFT(D510,MMULT(LEN(D510)-LEN(SUBSTITUTE(D510,{0,1,2,3,4,5,6,7,8,9},"")),{1;1;1;1;1;1;1;1;1;1 }))+0)
Pehle aap apne data ko dekhain k number text sa pehle aate hain. tu hum n number ko text sa alag karna ha jis k leye hum LEFT ka function use karte hain. K left par jo figures ya text ha us ko hum separate kar lain.Left k function k structure kuch is tarah hota hai.
=LEFT(string/cell refrence, Charachter to be separated)
Lekan ab masla ya ha k hamain nahee pata k number kitne hain ya ho sakta ha k string m left par 1 number ho ya 15 ho, k hum us ko seperate kar sakain.
Tu is maqsad l leye hum MMULT k function use karte hain. Ya array function hota ha jo k 2 Arrays ko multiply kar k us ka result deta ha (ARRAYS ko bahtar agar koi Maths ka expert ho tu wo explain kar dy ga)
Tu is k leye hum ko ya karna ho ga k pehle hum range m mujood har string k length maloom karain jis k leye hum na
(LEN(D510) ka function use kya ka har string ki length maloom ho jae.
Jab hamain string ki length maloom ho jae tu hum is m sa number ko delete kar k sirf text ki length maloom karain k kitne ha aur is ka result hum left function ko bhej dain k wo string m sa txt alag kar k hamain number return kary. Tu is k leye hum n Ya karna ha
Tu hum na sirf total length of string m sa txt ki length maloom karne k leye Substitutue ka function use kya
LEN(SUBSTITUTE(D510,{0,1,2,3,4,5,6,7,8,9},"")
Substitute string m mujood number ko empty space sa replace kar daita ha jis ko excel count nahee karta aur is tarah hamain string m txt ki length milte
LEN(D510)-LEN(SUBSTITUTE(D510,{0,1,2,3,4,5,6,7,8,9},"")
Agar aap ab is part ko dekhain tu saf pata chalta ha ka hum na total length-string m txt ki length ko minus kar rahe hain. Ab jo answer ata ha is ka result hasil karne k leye hum is ko MMULT function k zarya array {1;1;1;1;1;1;} sa multiply karte hain aur hamain text m mujood numbers ki length milte ha. Lekan masla ya ha k in MMULT aur LEN ka function hamain NUMBER return nahee karta tu is ko number m convert karne k leye hum har aik return hone wale figure m 0 add karte hain jis sa hamain left par mujood number ki position ya length number m milte ha.
Ab left k main function ko pata chalta ha k kitne number separate karne hain aur is ka jawab MAIN sumproduct k function ko jata ha aur in numbers ko sum karta ha. Chonka agar aap dekhain k ya sara formulas array par mushtamil ha aur sumproduct bhe arrays ki tarah he kam karta ha. ARRAYS formula ko execute karne k leye CTRL+SHIFT+ENTER press karte hain.

Hope thoda bohat aap ko samajh aa gae ho gi
regards

[miileeuko]

Bhi ager ap naraz na hon tu yahe formula ise sheet py laga k day sakty hian mian ny try keya hya but kam ne krh

just information kleya

[syar]

Bhi ager ap naraz na hon tu yahe formula ise sheet py laga k day sakty hian mian ny try keya hya but kam ne krh

just information kleya

Brother Formula lekhne ya copy karne ka bad sirf ENTER na karain blka
CTRL+SHIFT+ENTER ak sath press karin. phr fomula kam kare ga.

Formula m deye gai ranges apni marze k mutabiq adjust kar lain.

regards,

[waqaskhan722]

agree

[waqas urdu]

thanks says brother

[IT.BOY]