 |
 | 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 / August 2007Formula Question
I have the following formula in cell E2 =SUMPRODUCT(($B$395:$B$2043=1)*($G$395:$G$2043>0)) I have the same formula (almost) in cells E2 through U2 I want to drag this formula across those 17 cells but I need to change the =1 to =2, for cell F2, and =3 for G2, and =4 for F2, etc. Is there an easy way to do that other than going through each formula and changing it by hand? Thanks >I have the following formula in cell E2 > [quoted text clipped - 9 lines] > >Thanks I presume the =4 for F2 was a typo. Replace the =1 with =columns($A:A) As you drag it across, it will adjust. --ron > Replace the =1 with =columns($A:A) Won't that return wrong numbers if a new column B, C or D is inserted?  SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >>I have the following formula in cell E2 >> [quoted text clipped - 16 lines] > As you drag it across, it will adjust. > --ron No, that's the beauty of using columns/rows You don't need to anchor them like you do with column/row where you usually subtract to offset whenever a column/row is inserted SignatureRegards, Peo Sjoblom >> Replace the =1 with =columns($A:A) > [quoted text clipped - 20 lines] >> As you drag it across, it will adjust. >> --ron > No, that's the beauty of using columns/rows > > You don't need to anchor them like you do with column/row where you > usually subtract to offset whenever a column/row is inserted Not for me it doesn't in XL97. If I enter: =COLUMNS($A:A) In column E and drag it across to Column J it returns 1, 2,3, 4, 5, 6 as expected but if I now insert a new column C it changes to 1, 2, 4, 5, 6, 7 (in colums F:K of course) Is it different in later versions? (Why do these thing always come up when its past my bedtime <g> ) SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk You are correct, if you insert columns in C it will be skewed, if you insert columns in A (before the column sumproduct deals with SUMPRODUCT(($B$395:$B$2043=1) it works. I never thought about inserting columns in-between where the formula is and the column the formula deals with. I only use columns/rows when copying down array formulas to get more than one value from de facto lookups or when using offset/index to transpose certain layouts. and if you insert and rows/columns above/to the left of those formula columns/rows work as opposed to row/column Now go to bed and stop embarrassing me <bg> SignatureRegards, Peo Sjoblom >> No, that's the beauty of using columns/rows >> [quoted text clipped - 12 lines] > > (Why do these thing always come up when its past my bedtime <g> ) >> Replace the =1 with =columns($A:A) > >Won't that return wrong numbers if a new column B, C or D is inserted? That would depend on what the OP wants if he inserts columns. He might not want it to change to the right. --ron Try: =SUMPRODUCT(($B$395:$B$2043=COLUMN(E1)-COLUMN($D$1))*($G$395:$G$2043>0)) SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >I have the following formula in cell E2 > [quoted text clipped - 9 lines] > > Thanks Thanks for the help everyone. This solved my problem. >Try: > >=SUMPRODUCT(($B$395:$B$2043=COLUMN(E1)-COLUMN($D$1))*($G$395:$G$2043>0)) If you change it to COLUMN(A1), this will return 1 in the first cell. However, when copied across this will become COLUMN(B1) and will return 2 in the next column, then COLUMN(C1), returning 3, in the next column, and so on. Hope this helps. Pete > I have the following formula in cell E2 > [quoted text clipped - 9 lines] > > Thanks |
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.