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

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

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

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

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