 |
 | 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 / September 2006Count fields and show names
Hello all, I have an excel spreadsheet that has 5 fields (cells-HIP, WAIST, ARM, LEGS, HEAD) each with a value of 1 or 0 (True or False) I need a formua to count the 5 fields and if there are 3/5 that are 1 (True) then fill a new cell (Total) with 1(True), if there are less than 3 fields, then False (0). But would it also be possible to show which fields, along with the value of 1 or 0, were true and which were false? For example, 3/5 fields--->True False HIP, WAIST, HEAD ARMS, LEGS Thanks a million for you time! Hi slagg - the first part is simple in the total cell put ---- =if(count(range of the 5 cells)>3,"True"," ") or =if(count(range of the 5 cells)>3,"True","False") if you want text there Not sure exactly what you mean by showing which cells were true and which false -they are in the cells so - do you want then in another location? Could you explain a bith more -- Ta --- Dika > Hello all, > [quoted text clipped - 11 lines] > > Thanks a million for you time! Put the 5 heading in A1:E1 in F1 put 'Total', in G1 put 'True' and H1 put 'False' in F2 put =IF(SUM(A2:E2)>2,1,0) in G2 put =CONCATENATE(IF(A2,$A$1&", ",),IF(B2,$B$1&", ",),IF(C2,$C$1&", ",),IF(D2,$D$1&", ",),IF(E2,$E$1&", ",)) in H2 put =CONCATENATE(IF(A2,,$A$1&", "),IF(B2,,$B$1&", "),IF(C2,,$C$1&", "),IF(D2,,$D$1&", "),IF(E2,,$E$1&", ")) Let me know if this gives intended results Gerry > Hello all, > [quoted text clipped - 11 lines] > > Thanks a million for you time! WOW...thanks guys! I will ty it as soon as I get home. In theroy it is actually quite easy. Count or sum cells A1:E1(they will contain a 1 or 0-True/False) if ANY 3 of the 5 are true, then the final box will read "1" or "True", if less than 3 are true, then "False". But, it would be even better to show which of the 5 fields are true and which were false. Does that make sense? It is just for sums later on, to know which of the 3/5 trues was most common. Thanks a million guys! > Put the 5 heading in A1:E1 in F1 put 'Total', in G1 put 'True' and H1 put > 'False' [quoted text clipped - 24 lines] > > > > Thanks a million for you time! Thanks guys! Works like a charm! Thanks so much > WOW...thanks guys! I will ty it as soon as I get home. In theroy it is > actually quite easy. [quoted text clipped - 34 lines] > > > > > > Thanks a million for you time! OK guys, I have another problem I will post here and on another post if nobody reads this. I have the same 5 (A2:E2)fields, each with a 1 or 0. What I need this time is that If A2 is 1, then I need A2 plus any 2 (1or true) of the remaining four (B2:E2), to them have the new field box called total to read 1 or true. If A2 is 0(false), then regardless of the other four, the Total field must read 0 or false. Does this make sense? Thanks again! > Thanks guys! Works like a charm! > [quoted text clipped - 38 lines] > > > > > > > > Thanks a million for you time! |
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.