 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / November 2007Sum-Product-Question
This formula works: =SUMPRODUCT(--($C$17:$C$200<>"")*--($K$17:$K$200="bjh")) It returns an answer of 8, which is correct. But I need to drag this down for fifty columns. If I do that I'll have to change the part where I have "bjh" for all fifty rows. Can I somehow insert the cell it is referring to instead--O17 and then just drag it down? Obviously it didn't work when I tried it, but it must be a formatting problem. Appreciate this newsgroup try replacing your * with a ,  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > This formula works: > [quoted text clipped - 9 lines] > > Appreciate this newsgroup I just re-read and it does work and you can certainly copy down where cell o17 contains bjh If o18=sam then it would look for sam in col K. If you want it to look for bjh for 50 use $o$17 to make the reference absolute or just name it and use that. BTW, * or , both seem to work. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > This formula works: > [quoted text clipped - 9 lines] > > Appreciate this newsgroup I am inserting this formula in p17. In my original formula I have bjh, but I need to change this somehow to o17 in order to drag down. Do I just substitute bjh for o17? I tried it and it didn't work. I appreciate your help. >I just re-read and it does work and you can certainly copy down where cell >o17 contains bjh >If o18=sam then it would look for sam in col K. If you want it to look for >bjh for 50 use $o$17 to make the reference absolute or just name it and use >that. BTW, * or , both seem to work. Post YOUR formula efforts Both do the same =SUMPRODUCT(--($C$1:$C$200<>"")*--($K$1:$K$200=O2)) =SUMPRODUCT(--($C$1:$C$200<>"")*--($K$1:$K$200="bjh")) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >I am inserting this formula in p17. In my original formula I have > bjh, but I need to change this somehow to o17 in order to drag down. [quoted text clipped - 8 lines] >>use >>that. BTW, * or , both seem to work. Thank you for your patience. I found my error. I was putting quotation marks around the o2 and getting a zero value. Now it works fine. Thanks again. >Post YOUR formula efforts >Both do the same >=SUMPRODUCT(--($C$1:$C$200<>"")*--($K$1:$K$200=O2)) >=SUMPRODUCT(--($C$1:$C$200<>"")*--($K$1:$K$200="bjh")) Glad you finally got it sorted. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Thank you for your patience. I found my error. I was putting > quotation marks around the o2 and getting a zero value. Now it works [quoted text clipped - 6 lines] >>=SUMPRODUCT(--($C$1:$C$200<>"")*--($K$1:$K$200=O2)) >>=SUMPRODUCT(--($C$1:$C$200<>"")*--($K$1:$K$200="bjh"))
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.