Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using COUNTIF and AND functions together
Hoping someone can help me with this, I've read some related topics and
couldn't apply the principles to my own problem (might take me a while to explain)... I record all incidents and issues that arise within our business in a spreadsheet, with the following column headings. A - ENTERED BY [person entering places their initials here] B - DAY [1 - 31] C - MONTH [1 - 12] D - YEAR [1999 - 2006] E - FIN YEAR [98-99 - 05-06] F - SITE [Name of site] G - BUSINESS [Cleaning, Catering, Security, Hospitality] H - TYPE [Retail, Commercial, Industrial etc] I - MANAGER [Managers name] J - FIRST NAME [of person involved in incident] K - SURNAME [of person involved in incident] L - PRIMARY CODE [1-9 represents incident type, ie. 2=1st aid, 6= public liability] M - SECONDARY CODE [various numbers] N - ADDITIONAL CODE [if more than one secondary code is applicable] O - BODY PART [1 - 24, ie. 1=ankle, 23=wrist] P - DESCRIPTION [free text incident description] I want to create a table on another worksheet that can calculate statistics based on the information entered, currently the spreadsheet has 2309 rows. For example I want to write a formula to calculate how many first aid injuries the security division had in financial year 05-06, without filtering the list to work out? This is just one of the many statistics I'd like to produce and would be most grateful if anyone has any ideas. Second query, is there any way I can link the data to another worksheet, for example a security worksheet. So that if SECURITY is entered in column G the entire row will copy across to the security worksheet. If you haven't figured by now my knowledge is pretty basic so pls excuse if I am confused by your response. Thanks for any anticipated assistance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using COUNTIF and AND functions together
Look at pivot tables
See http://www.contextures.com/xlPivot01.html and http://www.peltierteh.com/Excel/Pivots/pivottables.htm -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Twinkle17" wrote in message ... Hoping someone can help me with this, I've read some related topics and couldn't apply the principles to my own problem (might take me a while to explain)... I record all incidents and issues that arise within our business in a spreadsheet, with the following column headings. A - ENTERED BY [person entering places their initials here] B - DAY [1 - 31] C - MONTH [1 - 12] D - YEAR [1999 - 2006] E - FIN YEAR [98-99 - 05-06] F - SITE [Name of site] G - BUSINESS [Cleaning, Catering, Security, Hospitality] H - TYPE [Retail, Commercial, Industrial etc] I - MANAGER [Managers name] J - FIRST NAME [of person involved in incident] K - SURNAME [of person involved in incident] L - PRIMARY CODE [1-9 represents incident type, ie. 2=1st aid, 6= public liability] M - SECONDARY CODE [various numbers] N - ADDITIONAL CODE [if more than one secondary code is applicable] O - BODY PART [1 - 24, ie. 1=ankle, 23=wrist] P - DESCRIPTION [free text incident description] I want to create a table on another worksheet that can calculate statistics based on the information entered, currently the spreadsheet has 2309 rows. For example I want to write a formula to calculate how many first aid injuries the security division had in financial year 05-06, without filtering the list to work out? This is just one of the many statistics I'd like to produce and would be most grateful if anyone has any ideas. Second query, is there any way I can link the data to another worksheet, for example a security worksheet. So that if SECURITY is entered in column G the entire row will copy across to the security worksheet. If you haven't figured by now my knowledge is pretty basic so pls excuse if I am confused by your response. Thanks for any anticipated assistance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|