Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I count rows with text values in several columns.
I need your help.
I have to group my answer by Platform and Size. Column 1 has several platform names ie. "bob". It contains duplicates. Column 2 contains text values such as small, meduim, large. My answer needs to be Bob Small = 1 Bob Medium = 20 Bob Large = 36. I can use cell text for the platform names and the sizes. This result will be used in a further financial calculation. I have tried Sumproduct and CountIf and am lost. Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I count rows with text values in several columns.
Hi
One way =SUMPRODUCT(--($A$1:$A$100="Bob"),--($B$1:$B$100="Small")) Better still Enter Small, Medium and Large in cells F1:H1 Enter Bob etc. in cells E2 downward in cell F2 =SUMPRODUCT(--($A$1:$A$100=$E2),--($B$1:$B$100=F$1)) Copy across and down as required Or, create a Pivot Table Assuming A1 contains Platform and B1 contains Size Place cursor in A1 DataPivot TableFinish On the PT skeleton that occurs on the newly inserted sheet Drag Platform to the Row area Drag Size to the Column area Drag Platform again, to the Data area where it will become Count of Platform. -- Regards Roger Govier "scmopar" wrote in message ... I need your help. I have to group my answer by Platform and Size. Column 1 has several platform names ie. "bob". It contains duplicates. Column 2 contains text values such as small, meduim, large. My answer needs to be Bob Small = 1 Bob Medium = 20 Bob Large = 36. I can use cell text for the platform names and the sizes. This result will be used in a further financial calculation. I have tried Sumproduct and CountIf and am lost. Bob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I count rows with text values in several columns.
Thanks a lot Roger, it worked.
Why the -- in the formula? "Roger Govier" wrote: Hi One way =SUMPRODUCT(--($A$1:$A$100="Bob"),--($B$1:$B$100="Small")) Better still Enter Small, Medium and Large in cells F1:H1 Enter Bob etc. in cells E2 downward in cell F2 =SUMPRODUCT(--($A$1:$A$100=$E2),--($B$1:$B$100=F$1)) Copy across and down as required Or, create a Pivot Table Assuming A1 contains Platform and B1 contains Size Place cursor in A1 DataPivot TableFinish On the PT skeleton that occurs on the newly inserted sheet Drag Platform to the Row area Drag Size to the Column area Drag Platform again, to the Data area where it will become Count of Platform. -- Regards Roger Govier "scmopar" wrote in message ... I need your help. I have to group my answer by Platform and Size. Column 1 has several platform names ie. "bob". It contains duplicates. Column 2 contains text values such as small, meduim, large. My answer needs to be Bob Small = 1 Bob Medium = 20 Bob Large = 36. I can use cell text for the platform names and the sizes. This result will be used in a further financial calculation. I have tried Sumproduct and CountIf and am lost. Bob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I count rows with text values in several columns.
Hi
Each cell in the range $A$1:$A$100="Bob" will return either True or False. Similarly for the range in B:B and Size. The double unary minus is one way of coercing False to 0 and 1 to True, so the resulting values can be used by Sumproduct The formula could have been written as =SUMPRODUCT(($A$1:$A$100="Bob")*($B$1:$B$100="Smal l")) where multiplying the results would have achieved the same coercion. False*False = 0*0 =0 False*True = 0*1 =0 True*False = 1*0 =0 True*True = 1*1 =1 Sumproduct adds all these results giving a count of when both cases are True -- Regards Roger Govier "scmopar" wrote in message ... Thanks a lot Roger, it worked. Why the -- in the formula? "Roger Govier" wrote: Hi One way =SUMPRODUCT(--($A$1:$A$100="Bob"),--($B$1:$B$100="Small")) Better still Enter Small, Medium and Large in cells F1:H1 Enter Bob etc. in cells E2 downward in cell F2 =SUMPRODUCT(--($A$1:$A$100=$E2),--($B$1:$B$100=F$1)) Copy across and down as required Or, create a Pivot Table Assuming A1 contains Platform and B1 contains Size Place cursor in A1 DataPivot TableFinish On the PT skeleton that occurs on the newly inserted sheet Drag Platform to the Row area Drag Size to the Column area Drag Platform again, to the Data area where it will become Count of Platform. -- Regards Roger Govier "scmopar" wrote in message ... I need your help. I have to group my answer by Platform and Size. Column 1 has several platform names ie. "bob". It contains duplicates. Column 2 contains text values such as small, meduim, large. My answer needs to be Bob Small = 1 Bob Medium = 20 Bob Large = 36. I can use cell text for the platform names and the sizes. This result will be used in a further financial calculation. I have tried Sumproduct and CountIf and am lost. Bob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I count rows with text values in several columns.
Thanks, I also tried teh Pivot table. After I got used to it, it was nice.
Hard to understand.. But easier to recreate if the input data format changes. "Roger Govier" wrote: Hi Each cell in the range $A$1:$A$100="Bob" will return either True or False. Similarly for the range in B:B and Size. The double unary minus is one way of coercing False to 0 and 1 to True, so the resulting values can be used by Sumproduct The formula could have been written as =SUMPRODUCT(($A$1:$A$100="Bob")*($B$1:$B$100="Smal l")) where multiplying the results would have achieved the same coercion. False*False = 0*0 =0 False*True = 0*1 =0 True*False = 1*0 =0 True*True = 1*1 =1 Sumproduct adds all these results giving a count of when both cases are True -- Regards Roger Govier "scmopar" wrote in message ... Thanks a lot Roger, it worked. Why the -- in the formula? "Roger Govier" wrote: Hi One way =SUMPRODUCT(--($A$1:$A$100="Bob"),--($B$1:$B$100="Small")) Better still Enter Small, Medium and Large in cells F1:H1 Enter Bob etc. in cells E2 downward in cell F2 =SUMPRODUCT(--($A$1:$A$100=$E2),--($B$1:$B$100=F$1)) Copy across and down as required Or, create a Pivot Table Assuming A1 contains Platform and B1 contains Size Place cursor in A1 DataPivot TableFinish On the PT skeleton that occurs on the newly inserted sheet Drag Platform to the Row area Drag Size to the Column area Drag Platform again, to the Data area where it will become Count of Platform. -- Regards Roger Govier "scmopar" wrote in message ... I need your help. I have to group my answer by Platform and Size. Column 1 has several platform names ie. "bob". It contains duplicates. Column 2 contains text values such as small, meduim, large. My answer needs to be Bob Small = 1 Bob Medium = 20 Bob Large = 36. I can use cell text for the platform names and the sizes. This result will be used in a further financial calculation. I have tried Sumproduct and CountIf and am lost. Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Values in Rows with Partial Values in Columns | Excel Worksheet Functions | |||
I would like to Count values in two columns. | Excel Worksheet Functions | |||
How do I count values across multiple columns? | Excel Worksheet Functions | |||
How to Count Rows with defined values in multiple columns | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |