 |
 | 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 / Worksheet Functions / March 2008Sum per row and Average per column from a second sheet.
OK. I have 4 colums in my table on sheet 2. In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it contains anything) add columns 2, 3, and 4 together for that row. Then Average these sums in one total. 1 2 3 4 ------------------------ A|Client1|10|20|30 B|Client2|10|20|30 C| |10|20|30 D|Client4|10|20|30 We should get the following. A| 10+20+30=60 B| 10+20+30=60 C| 0 (Because column 1 is empty) D| 10+20+30=60 ------------------------- 60 +60 +60 ----- 180 / 3=60 Average score or each row, 60. Does that make sense, and if so, how can I make it happen? Create column 5 in sheet one with the formula IF(A1="",0,Sum(A2:A4)) Then copy that formula down To get the average write the formula in a cell =Sum(E1:E4)/Countif(E1:E4,">0") I'm getting kind of mixed up of how you have the numbers as your column headings and letters as rows. But E should be the fifth column. > OK. I have 4 colums in my table on sheet 2. > In the cell on sheet 1 I want to look at Sheet 2, Column 1 and (if it [quoted text clipped - 23 lines] > > Does that make sense, and if so, how can I make it happen? I appologise, I got my row and column identifiers reversed. Your plan DID work, thank you. Is there anyway I can doe the whole operation in a singe cell? Just for my own curiosity. - Thank you! > Create column 5 in sheet one with the formula > [quoted text clipped - 35 lines] > > > > Does that make sense, and if so, how can I make it happen? Try this: =SUMPRODUCT((A1:A4<>"")*B1:D4)/COUNTA(A1:A4)  SignatureBiff Microsoft Excel MVP >I appologise, I got my row and column identifiers reversed. > Your plan DID work, thank you. [quoted text clipped - 42 lines] >> > >> > Does that make sense, and if so, how can I make it happen? hahaha, there are things you can do... but some are more complicated then just doing it the way you have it now. But here is a fairly complicated formula that would do what you are asking for with the data you presented =SUM(IF(LEN(A1:A4)>0,B1:D4))/SUM(IF(LEN(A1:A4)>0,1,0)) If you put the formula in to a cell... You HAVE to enter it by Holding down CTRL+SHIFT+ENTER Otherwise you will get an error. Let me know if that works. > I appologise, I got my row and column identifiers reversed. > Your plan DID work, thank you. [quoted text clipped - 42 lines] > > > > > > Does that make sense, and if so, how can I make it happen? LOL. You win. My Brain just exploded. I'll spend the rest of the year breaking that up and reverse enginerring it to wrap my head around it. ROFL. > hahaha, there are things you can do... but some are more complicated then > just doing it the way you have it now. But here is a fairly complicated [quoted text clipped - 55 lines] > > > > > > > > Does that make sense, and if so, how can I make it happen? haha, yea that was quite a mess. If you look at T Valko's equation, that is a much more user friendly equation that is much more reliable then mine, haha. Good luck! > LOL. You win. My Brain just exploded. > [quoted text clipped - 60 lines] > > > > > > > > > > Does that make sense, and if so, how can I make it happen? |
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.