HI Noorain!! This is awsome maan... This shows you are not only an expert in excel but also have a poetic angle to this mastery!!!
@ Aayush!! This brings on an idea to create a list of most used formula in excel by our users.. Cheers! Andy 2011/3/11 Ayush Jain <[email protected]> > Noorain, You are always awesome and this poem is phenomenal. > > Regards > Ayush Jain > > 2011/3/11 NOORAIN ANSARI <[email protected]> > >> Dear Experts, >> >> I have tried to describe importance of sumproduct function in my poem >> with example. >> Please read and see attached worksheet..... >> >> SUMPRODUCT है एक..लेकिन इसके रूप है अनेक. >> अगर बिश्वास नहीं है भाई तो जरा एक नज़र तो देख. >> नहीं चाहिए हमको बहूत सारे Functions का सहारा. >> उनके जगह पे काफी है बस एक sumproduct हमारा. >> चाहे DATA हो आसान,चाहे हो critical. >> चाहे हो Horizental ,चाहे हो Vertical. >> चाहे EXCEL हो 2003, चाहे हो 2007. >> इसके लिए issue नहीं है कोई भी हालात. >> कौन नहीं रखेगा excel के ऐसे धांसू >> function को याद. >> जो बिना CTRL+SHIFT+Enter का ही देता है >> Array Function का स्वाद. >> हर जगह इसका Performance हैं शानदार. >> Best Function के अवार्ड का ये है हकदार. >> महिमा sumproduct का भईया अपरम्पार है. >> तभी तो हर किसी के दिल में इसके लिए प्यार >> है. >> >> Name ID Marks Check Duplicate Entry Helping Col A 111 123 >> =SUMPRODUCT(--($A$2:A5=A5)) 1 B 222 232 =SUMPRODUCT(--($A$2:A6=A6)) 1 A >> 111 453 =SUMPRODUCT(--($A$2:A7=A7)) 2 D 444 231 >> =SUMPRODUCT(--($A$2:A8=A8)) 1 A 999 124 =SUMPRODUCT(--($A$2:A9=A9)) 3 B >> 555 234 =SUMPRODUCT(--($A$2:A10=A10)) 2 C 333 321 >> =SUMPRODUCT(--($A$2:A11=A11)) 1 B 222 256 =SUMPRODUCT(--($A$2:A12=A12)) >> 3 D 444 231 =SUMPRODUCT(--($A$2:A13=A13)) 2 Formula Criteria Syntex >> Example Output Countif A =SUMPRODUCT(--(A2:A10=B13)) 3 Countifs A,111 >> =SUMPRODUCT((A2:A10=B14)*(B2:B10=111)) 2 Sum =SUMPRODUCT(C2:C10) >> 2205 Sumif A =SUMPRODUCT((A2:A10=B16)*(C2:C10)) 700 Sumifs A,111 >> =SUMPRODUCT((A2:A10="A")*(B2:B10=111)*(C2:C10)) 576 Averageif A >> =SUMPRODUCT((A2:A10="A")*(C2:C10))/SUMPRODUCT(--(A2:A10="A")) 233.3333333 >> Averageifs >> A,111 >> =SUMPRODUCT((A2:A10="A")*(B2:B10=111)*(C2:C10))/SUMPRODUCT((A2:A10="A")*(B2:B10=111)) >> 288 Conditional MAX Max of A =SUMPRODUCT(MAX((A2:A10="A")*(C2:C10))) >> 453 Max Value A & B A,B >> =SUMPRODUCT(MAX(($A$2:$A$10={"A","B"})*($C$2:$C$10))) 453 Conditional >> Large 2nd Max of A =SUMPRODUCT(LARGE((A2:A10="A")*(C2:C10),2)) 124 Sum >> of A & B A, B =SUMPRODUCT((A2:A10={"A","B"})*(C2:C10)) 1422 Count of >> A&B A,B =SUMPRODUCT(--(A2:A10={"A","B"})) 6 All Marks of A A >> =SUMPRODUCT(($A$2:$A$10="A")*($D$2:$D$10=ROW(A1))*($C$2:$C$10)) 123 >> =SUMPRODUCT(($A$2:$A$10="A")*($D$2:$D$10=ROW(A2))*($C$2:$C$10)) 453 >> =SUMPRODUCT(($A$2:$A$10="A")*($D$2:$D$10=ROW(A3))*($C$2:$C$10)) 124 Total >> Unique Data >> =SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10)) Press ctrl+shift+enter 4 >> >> If i missed any thing please add... >> -- >> Thanks & regards, >> Noorain Ansari >> >> > > > -- > Best regards, > Ayush Jain > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to [email protected] > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- Warm Regards, Aindril De Unified Learning Pvt Ltd. Ph: 9811300157 -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
