| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting based on two conditions in excel 2003
I've a sheet with two option buttons (grouped). The data goes like
A B C Name Process Role x a Agent y a Agent z a Agent m b TL n b MIS o a MIS p a TL q b Agent I want results in a table where results are dependent on option button the user selects. e.g. if the user selects Option 1 which is for process a Agent 3 TL 1 MIS 1 similarly if the user select option 2 which is for process b the result should be Agent 1 TL 1 MIS 1 How can this be achieved using a formula in Excel 2003. I don't know much of VBA still if code is there it would also be welcome Thanks, Anand |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting based on two conditions in excel 2003
Try
=SUMPRODUCT(--(B2:B20="a"),--(C2:C20="TL") You will need t adapt to the cells that contain those values. -- HTH Bob "Anand" wrote in message ... I've a sheet with two option buttons (grouped). The data goes like A B C Name Process Role x a Agent y a Agent z a Agent m b TL n b MIS o a MIS p a TL q b Agent I want results in a table where results are dependent on option button the user selects. e.g. if the user selects Option 1 which is for process a Agent 3 TL 1 MIS 1 similarly if the user select option 2 which is for process b the result should be Agent 1 TL 1 MIS 1 How can this be achieved using a formula in Excel 2003. I don't know much of VBA still if code is there it would also be welcome Thanks, Anand |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting based on two conditions in excel 2003
Hmmm....
Since the results are based on an Option button let me try this function with an if... something like: =IF(A3=1,SUMPRODUCT(--(B2:B20="a"),-- (C2:C20="TL"),IF(A3=2,SUMPRODUCT(--(B2:B20="b"),--(C2:C20="TL"),"")) Let me give this a try I'll get back soon... Thanks for your help, Anand On Mar 25, 2:29*am, "Bob Phillips" wrote: Try =SUMPRODUCT(--(B2:B20="a"),--(C2:C20="TL") You will need t adapt to the cells that contain those values. -- HTH Bob "Anand" wrote in message ... I've a sheet with two option buttons (grouped). The data goes like A * * * * * * B * * * * * * * * C Name * * *Process * * * Role x * * * * * * *a * * * * * * * * *Agent y * * * * * * *a * * * * * * * * *Agent z * * * * * * *a * * * * * * * * *Agent m * * * * * * b * * * * * * * * *TL n * * * * * * *b * * * * * * * * *MIS o * * * * * * *a * * * * * * * * *MIS p * * * * * * *a * * * * * * * * *TL q * * * * * * *b * * * * * * * * * Agent I want results in a table where results are dependent on option button the user selects. *e.g. if the user selects Option 1 which is for process a Agent 3 TL * * *1 MIS * * 1 similarly if the user select option 2 which is for process b the result should be Agent *1 TL * * * 1 MIS * * *1 How can this be achieved using a formula in Excel 2003. I don't know much of VBA still if code is there it would also be welcome Thanks, Anand |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting based on two conditions in excel 2003
Thanks for your help... I modified the formula a bit and was able to
achieve my required result... thanks for guiding in the right direction I used this: =IF($I$1=1,SUMPRODUCT(--($B$3:$B$26="A"),--($C$3:$C$26="TL")),IF($I $1=2,SUMPRODUCT(--($B$3:$B$26="B"),--($C$3:$C$26="TL")),"")) Thanks again, Anand On Mar 25, 2:51*am, Anand wrote: Hmmm.... Since the results are based on an Option button let me try this function with an if... something like: =IF(A3=1,SUMPRODUCT(--(B2:B20="a"),-- (C2:C20="TL"),IF(A3=2,SUMPRODUCT(--(B2:B20="b"),--(C2:C20="TL"),"")) Let me give this a try I'll get back soon... Thanks for your help, Anand On Mar 25, 2:29*am, "Bob Phillips" wrote: Try =SUMPRODUCT(--(B2:B20="a"),--(C2:C20="TL") You will need t adapt to the cells that contain those values. -- HTH Bob "Anand" wrote in message .... I've a sheet with two option buttons (grouped). The data goes like A * * * * * * B * * * * * * * * C Name * * *Process * * * Role x * * * * * * *a * * * * * * * * *Agent y * * * * * * *a * * * * * * * * *Agent z * * * * * * *a * * * * * * * * *Agent m * * * * * * b * * * * * * * * *TL n * * * * * * *b * * * * * * * * *MIS o * * * * * * *a * * * * * * * * *MIS p * * * * * * *a * * * * * * * * *TL q * * * * * * *b * * * * * * * * * Agent I want results in a table where results are dependent on option button the user selects. *e.g. if the user selects Option 1 which is for process a Agent 3 TL * * *1 MIS * * 1 similarly if the user select option 2 which is for process b the result should be Agent *1 TL * * * 1 MIS * * *1 How can this be achieved using a formula in Excel 2003. I don't know much of VBA still if code is there it would also be welcome Thanks, Anand |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting based on two conditions in excel 2003
You can do it a bit more simply, without the IF
=SUMPRODUCT(--($B$3:$B$26=CHOOSE($I$1,"A","B")),--($C$3:$C$26="TL")) -- HTH Bob "Anand" wrote in message ... Thanks for your help... I modified the formula a bit and was able to achieve my required result... thanks for guiding in the right direction I used this: =IF($I$1=1,SUMPRODUCT(--($B$3:$B$26="A"),--($C$3:$C$26="TL")),IF($I $1=2,SUMPRODUCT(--($B$3:$B$26="B"),--($C$3:$C$26="TL")),"")) Thanks again, Anand On Mar 25, 2:51 am, Anand wrote: Hmmm.... Since the results are based on an Option button let me try this function with an if... something like: =IF(A3=1,SUMPRODUCT(--(B2:B20="a"),-- (C2:C20="TL"),IF(A3=2,SUMPRODUCT(--(B2:B20="b"),--(C2:C20="TL"),"")) Let me give this a try I'll get back soon... Thanks for your help, Anand On Mar 25, 2:29 am, "Bob Phillips" wrote: Try =SUMPRODUCT(--(B2:B20="a"),--(C2:C20="TL") You will need t adapt to the cells that contain those values. -- HTH Bob "Anand" wrote in message ... I've a sheet with two option buttons (grouped). The data goes like A B C Name Process Role x a Agent y a Agent z a Agent m b TL n b MIS o a MIS p a TL q b Agent I want results in a table where results are dependent on option button the user selects. e.g. if the user selects Option 1 which is for process a Agent 3 TL 1 MIS 1 similarly if the user select option 2 which is for process b the result should be Agent 1 TL 1 MIS 1 How can this be achieved using a formula in Excel 2003. I don't know much of VBA still if code is there it would also be welcome Thanks, Anand |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting with conditions based on another range | Excel Worksheet Functions | |||
| Excel 2003 function for counting rows with two conditions. | Excel Worksheet Functions | |||
| Counting based upon 2 conditions that are text based | Excel Discussion (Misc queries) | |||
| Looking for experience with counting and conditions in excel 2003 | Excel Worksheet Functions | |||
| counting based on 2 conditions | Excel Discussion (Misc queries) | |||
Hybrid Mode
