Worksheet formulars
I am using Excell to extract and interigate information provided to me in a
spreadsheet format of fire rescue related incidents to allow me to identify trends etc. The spreadsheet consists of headed columns. What I am trying to do is excell to look in different columns for a criteria and then tell me how many entries match the criter. For exmple Column Q may be a list of areas within the county, Column K may be a list of incident types, m- a list of causes, j types of property and P may be numbered category that the property type belongs. What I need it to do is count how many rows match a criteria such as Dwelling fires (identified as "-1" in P), Fire listed in column K, of suspicious origin - Column M, in the area of Surrey Heath (listed in column Q) I would very much appriciate any help anyone can offer -- pk |
Worksheet formulars
I'd probably do this with a PIVOT TABLE.
Go to MVPS.org and do a search for PIVOT Tables. I'd do it, but I need to restart my computer to do it right now. "PK" wrote in message ... I am using Excell to extract and interigate information provided to me in a spreadsheet format of fire rescue related incidents to allow me to identify trends etc. The spreadsheet consists of headed columns. What I am trying to do is excell to look in different columns for a criteria and then tell me how many entries match the criter. For exmple Column Q may be a list of areas within the county, Column K may be a list of incident types, m- a list of causes, j types of property and P may be numbered category that the property type belongs. What I need it to do is count how many rows match a criteria such as Dwelling fires (identified as "-1" in P), Fire listed in column K, of suspicious origin - Column M, in the area of Surrey Heath (listed in column Q) I would very much appriciate any help anyone can offer -- pk |
Worksheet formulars
You can use the sumproduct formula to count (or total) multiple conditions;
check http://www.xldynamic.com/source/xld.SUMPRODUCT.html You might also look at the DCOUNT function. "PK" wrote: I am using Excell to extract and interigate information provided to me in a spreadsheet format of fire rescue related incidents to allow me to identify trends etc. The spreadsheet consists of headed columns. What I am trying to do is excell to look in different columns for a criteria and then tell me how many entries match the criter. For exmple Column Q may be a list of areas within the county, Column K may be a list of incident types, m- a list of causes, j types of property and P may be numbered category that the property type belongs. What I need it to do is count how many rows match a criteria such as Dwelling fires (identified as "-1" in P), Fire listed in column K, of suspicious origin - Column M, in the area of Surrey Heath (listed in column Q) I would very much appriciate any help anyone can offer -- pk |
Worksheet formulars
I would suggest using the AutoFilter. Highlight all of the column headings
you want to be able to sort by. Then select "Data" from the menu bar and then "Filter" then "AutoFilter". Now, you should see dropdown arrows in each of your column headers. Simply select the criteria for each column. If you need a formula to count the displayed number of columns, you can use this: =SUBTOTAL(102,A:A) I'd suggest putting it in Row 1 after all of your headers so that it never gets filtered with the rest of your data. HTH, Elkar "PK" wrote: I am using Excell to extract and interigate information provided to me in a spreadsheet format of fire rescue related incidents to allow me to identify trends etc. The spreadsheet consists of headed columns. What I am trying to do is excell to look in different columns for a criteria and then tell me how many entries match the criter. For exmple Column Q may be a list of areas within the county, Column K may be a list of incident types, m- a list of causes, j types of property and P may be numbered category that the property type belongs. What I need it to do is count how many rows match a criteria such as Dwelling fires (identified as "-1" in P), Fire listed in column K, of suspicious origin - Column M, in the area of Surrey Heath (listed in column Q) I would very much appriciate any help anyone can offer -- pk |
Worksheet formulars
Google Search: pivot.table site:mvps.org
might be a good way to start given all the sites that reproduce newsgroup postings, but once you find your way to 10 (soon to be 11) links at http://www.mvps.org/dmcritchie/excel...tm#pivottables I think you are going to end up at: the index page at Contextures.com http://www.contextures.com/tiptech.html and to a Flash (Macromedia) presentation for an overview at http://www.datapigtechnologies.com/ExcelMain.htm Surprise there is more than one Flash presentation, but between those two sites it would be hard to find better general information on Pivot Tables. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Barb Reinhardt" wrote in message ... I'd probably do this with a PIVOT TABLE. Go to MVPS.org and do a search for PIVOT Tables. I'd do it, but I need to restart my computer to do it right now. "PK" wrote in message ... I am using Excell to extract and interigate information provided to me in a spreadsheet format of fire rescue related incidents to allow me to identify trends etc. The spreadsheet consists of headed columns. What I am trying to do is excell to look in different columns for a criteria and then tell me how many entries match the criter. For exmple Column Q may be a list of areas within the county, Column K may be a list of incident types, m- a list of causes, j types of property and P may be numbered category that the property type belongs. What I need it to do is count how many rows match a criteria such as Dwelling fires (identified as "-1" in P), Fire listed in column K, of suspicious origin - Column M, in the area of Surrey Heath (listed in column Q) I would very much appriciate any help anyone can offer -- pk |
All times are GMT +1. The time now is 04:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com