|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 25th, 2010, 08:20 AM
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
March 25th, 2010, 09:29 AM
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
March 25th, 2010, 09:51 AM
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
March 25th, 2010, 10:12 AM
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
March 25th, 2010, 11:42 AM
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
