Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complex count
I have two columns in a spreadsheet. One column has one word descriptions
e.g. falls, and the other column has the location e.g 100 hall. I want to compile the number of falls for example that occurred on different halls e.g 100, 200, 300 halls. Remember that the column with descriptions has several descriptions but I just want falls. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complex count
=SUMPRODUCT(--(A2:A200="falls"),--(B2:B200=100))
etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "FSmitty" wrote in message ... I have two columns in a spreadsheet. One column has one word descriptions e.g. falls, and the other column has the location e.g 100 hall. I want to compile the number of falls for example that occurred on different halls e.g 100, 200, 300 halls. Remember that the column with descriptions has several descriptions but I just want falls. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complex count
What result would you expect from this sample:
fall..........100 hall fall..........100 hall full..........100 ball fall..........200 hall fall..........200 hall fall..........300 hall Biff "FSmitty" wrote in message ... I have two columns in a spreadsheet. One column has one word descriptions e.g. falls, and the other column has the location e.g 100 hall. I want to compile the number of falls for example that occurred on different halls e.g 100, 200, 300 halls. Remember that the column with descriptions has several descriptions but I just want falls. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complex count
Biff,
The column with 'falls' has numerous other classifications such as 'tears', 'skin', 'bruises' etc. I want a formual that will count only falls for lets say the 100 hall. Thanks for your help. "Biff" wrote: What result would you expect from this sample: fall..........100 hall fall..........100 hall full..........100 ball fall..........200 hall fall..........200 hall fall..........300 hall Biff "FSmitty" wrote in message ... I have two columns in a spreadsheet. One column has one word descriptions e.g. falls, and the other column has the location e.g 100 hall. I want to compile the number of falls for example that occurred on different halls e.g 100, 200, 300 halls. Remember that the column with descriptions has several descriptions but I just want falls. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complex count
Bob,
Now to add to the formula, Lets say that the 100 hall has a range of room numbers from 100 to 199. I want to count all the "falls" that occurred within that range of numbers (100 to 199). When I use the formula =SUMPRODUCT(--(A:A200="falls"),--(B2:B20099<200)), I get #VALUE as an answer to the formula instead of a count. "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200="falls"),--(B2:B200=100)) etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "FSmitty" wrote in message ... I have two columns in a spreadsheet. One column has one word descriptions e.g. falls, and the other column has the location e.g 100 hall. I want to compile the number of falls for example that occurred on different halls e.g 100, 200, 300 halls. Remember that the column with descriptions has several descriptions but I just want falls. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complex count
Hi!
falls 100 hall Is that data in 3 cells? Your original post makes it sound as though it's only in 2 cells: 1 cell = falls 1 cell = 100 hall Biff "FSmitty" wrote in message ... Bob, Now to add to the formula, Lets say that the 100 hall has a range of room numbers from 100 to 199. I want to count all the "falls" that occurred within that range of numbers (100 to 199). When I use the formula =SUMPRODUCT(--(A:A200="falls"),--(B2:B20099<200)), I get #VALUE as an answer to the formula instead of a count. "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200="falls"),--(B2:B200=100)) etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "FSmitty" wrote in message ... I have two columns in a spreadsheet. One column has one word descriptions e.g. falls, and the other column has the location e.g 100 hall. I want to compile the number of falls for example that occurred on different halls e.g 100, 200, 300 halls. Remember that the column with descriptions has several descriptions but I just want falls. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complex count
Hi again,
The data is located in two columns. One column has incidents e.g. falls, tears, bruises, etc. while the other column is the room number which range from 1 to 399. I want to count the # of falls per wing (100 hall) which would be any fall occurring for anyone in room 100 through 199. Hope this helps. Thanks. Brent "Biff" wrote: Hi! falls 100 hall Is that data in 3 cells? Your original post makes it sound as though it's only in 2 cells: 1 cell = falls 1 cell = 100 hall Biff "FSmitty" wrote in message ... Bob, Now to add to the formula, Lets say that the 100 hall has a range of room numbers from 100 to 199. I want to count all the "falls" that occurred within that range of numbers (100 to 199). When I use the formula =SUMPRODUCT(--(A:A200="falls"),--(B2:B20099<200)), I get #VALUE as an answer to the formula instead of a count. "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200="falls"),--(B2:B200=100)) etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "FSmitty" wrote in message ... I have two columns in a spreadsheet. One column has one word descriptions e.g. falls, and the other column has the location e.g 100 hall. I want to compile the number of falls for example that occurred on different halls e.g 100, 200, 300 halls. Remember that the column with descriptions has several descriptions but I just want falls. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complex count
Ok, you can make this much easier if you split the data into 3 columns:
falls | 100 | hall To count falls, 100, hall: =SUMPRODUCT(--(A1:A10="falls"),--(B1:B10=100),--(C1:C10="hall")) To count falls, =100, <=199, hall: =SUMPRODUCT(--(A1:A10="falls"),--(B1:B10=100),--(B1:B10<=199),--(C1:C10="hall")) It's better if you use cells to hold the different criteria: D1 = falls D2 = hall E1 = 100 F1 = 199 =SUMPRODUCT(--(A1:A10=D1),--(B1:B10=E1),--(C1:C10=D2)) =SUMPRODUCT(--(A1:A10=D1),--(B1:B10=E1),--(B1:B10<=F1),--(C1:C10=D2)) Biff "FSmitty" wrote in message ... Hi again, The data is located in two columns. One column has incidents e.g. falls, tears, bruises, etc. while the other column is the room number which range from 1 to 399. I want to count the # of falls per wing (100 hall) which would be any fall occurring for anyone in room 100 through 199. Hope this helps. Thanks. Brent "Biff" wrote: Hi! falls 100 hall Is that data in 3 cells? Your original post makes it sound as though it's only in 2 cells: 1 cell = falls 1 cell = 100 hall Biff "FSmitty" wrote in message ... Bob, Now to add to the formula, Lets say that the 100 hall has a range of room numbers from 100 to 199. I want to count all the "falls" that occurred within that range of numbers (100 to 199). When I use the formula =SUMPRODUCT(--(A:A200="falls"),--(B2:B20099<200)), I get #VALUE as an answer to the formula instead of a count. "Bob Phillips" wrote: =SUMPRODUCT(--(A2:A200="falls"),--(B2:B200=100)) etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "FSmitty" wrote in message ... I have two columns in a spreadsheet. One column has one word descriptions e.g. falls, and the other column has the location e.g 100 hall. I want to compile the number of falls for example that occurred on different halls e.g 100, 200, 300 halls. Remember that the column with descriptions has several descriptions but I just want falls. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count using complex criteria | Excel Worksheet Functions | |||
complex count question | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions |