1
--------------
Assalamalykum
Ok lets start our first class
attendence ist challo jaldi jaldi present sir bolo
OK..Here is the link go and download this workbook named Assignment # 01 Summarizing Data with Formulas from the following link
http://cid-1cb4bb4c88c79d88.skydrive.live....cel%202007?uc=1
hopefully you have installed ms excel 2007 ..
because there are so many formulas which doesnt work in 2003
yes there are alternative ways to perform such tasks in 2003
buts its bit complicated ....
kheir
before we start let me tell you its just the small things which can solve big big problems
so make sure from the very begning that your data should be arranged in the proper manner
in order to get the maximum out put from it
okay let us know what is the proper form of data
your data should be in records ...
see this
see on the left every row has a proper record
you have cloumns in excel ..that are calleds ranges
then you have rows there ..this is called records
go into the sheet "Sumif & Sumproduct"
start seeing form F Coulmn Then go to the 3rd Row It Says Trans Number , Name, date, Product, Units, Dollar, Location
These are called Headers , and under this every row has a complete record i repeat a complete record
see row # 4 it says at tran # one Mr Ali Has Sold 45 Printers at 4 jan 04 at east region for 137.20 dollars
Thats a complete record, after this you should not have Blanks rows between records...
Assigning Names to the ranges..
jis range mei hamara data para hoa hei to we can assign them names ..takey formulas mei assani rahey
yahan is pic mei dikhiyye
left top per names likha hoa a raha hei kyun k yeh pori range mei ney select ker k isey
name aasign kiya hoa hei ab mei jahan bhi kisi formula mei name ka ref doun ga tow excel knows k i am talking about the
whole range which consist of 1900 names almost mujhey batana nahi parey ga k e4 sey ley e1900 tak check karo
it means name = g4:g1900 in our case
well app k pass jo sheet hei us mei names already assign hoye hoye hein ...press f3
yeh window pop up ho jaye gee showing you all the names somebody has assgined in the sheet
app in sab ko delete ker dein phir area select ker k fomula bar k left per click karein
aur apni selection ko jo bhi name assign kerna chahein ker dein ...assan tareeka yeh hei k
if you have proper header then select all the data & press ctrl+shift+F3 see following image
to it is asking you k kiya naam doun top row? yes humein headers hi to chahiyein yahan check lagayein aur ok ker
dein ab tamam fields ko name headers waley name assign ho jayein gey make sure k appp tamam data select ker k karein yeh
in our case f4 sey ley ker i guess around l1900 tak tamam data select kerein
asan tareeka selection ka yeh hei k f4 per click karein then crtl+shift+right arrow & then down arrow poori
selection ho jaye gee
ab left side pey a jayein isi sheet k
b5 pey ayiye ali k samney can you sum how many dollars sales ali has done?
ok type here =sumif(names,a5,dollars) & then enter
so you got the answer but how see names are the whole range that is from g4:g1903
yeh to ho gai criteria range then it is asking you about criteria kk app ka criteria kiya hei?
is range mei sey excel kiya ectract karey ? to hum ney kaha a5 mei jo likha hei usey dekho pori range mei
aur jahan jahan woh naam miley us key dollars ka sum ker do ...so simple -- (You may also write Ali in double quotes "ALI" in criteria section
but then you wouldnt be able to copy paste this formula )
ab baki sarey app sey ho jayein gey --
bilkul aisey hi countif work kerta hei simply it doesnt asks you about sum range
kyun k woh bs count ker k dey dey ga k ali na name is range mei kitni dafa aya hei
okay ab right side pey aye can you calculate how many laptops ali has sold ?????
sumif sey app ney total sales to nikal lein ali ki lekin what about sales of laptops?
and so on?
okay is k liye wesey to kafi funcations hein lekin 2007 mei excel has introduced very easy function
through sumifs yes write =sumifs(dollars,names,"Ali",products,"laptop") and hit enter
ho gaya? now you can have multiple criteria in 2007 up to 127 criterias ..its simply amazing
see this img
app dekhein mei ney criteria one mei ali nahi likha balkey o5 ka ref dey diya hei O cloumn mei
sales reps k names hein or O5 sey pehley $ ka matlab hei k mei ney coulmn lock ker diya ab ager mei is ko
right pey copy karoun ga to yeh hamesha o5 ko hi dekhey ge similarly criteria 2 mei p4 yani laptop
or p k baad $ ka matlab hei k mei ney row lock ker dee hei yani k ab ager mei iss ko down copy karoun ga to
yeh hamesha p mei hi dekhey ga nechey nahi aye ga ...
aur grand totals k liye lemme tel you app nechey aur samney sey totals hata dein then sara numerical data select karein
then one coulmn to right and one rows down select karein and press alt = ho gaya sum
abhi k liye itna bohat hei =P
baki jo kaaam hum ney nahi kiya yani nechey multipla countifs aur baki k
jo bhi formulas pochey gaye hein sheet mei app usey solve ker k upload kijiye jaldi jaldi --
aur ager app formula mei koi name call kerwana chahiyein ..kyuun k typing is not a good option..
to app formula edit mode mei rehtey hoye hi f3 press karein wur wahan sey select ker lein
WasAlaM
Appreciated work 
