counting based on two conditions in excel 2003

  • Thread starter Thread starter Anand
  • Start date Start date
A

Anand

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
 
Try

=SUMPRODUCT(--(B2:B20="a"),--(C2:C20="TL")

You will need t adapt to the cells that contain those values.
 
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
 
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
 
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

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
 
Back
Top