Excel count query

[abirami]

I have a query. Kindly check the below example.

In colum A, I have the following records
ramu
somu
geetha
ramu
somu
geetha
ramu
geetha
somu
In column B, I have the following records
pass
fail
fail
fail
pass
fail
fail
fail
pass

Now, I want to know, how many times did they fail depending the number of times their names appear in column A

Kindly anyone provide solution ASAP, I'm waiting for it

[Okkitrooi]

try in Column C

=SUM(($A$1:$A$10=A1)*($B$1:$B$10="Fail"))

exit with Shift+Ctrl+Enter

[davesexcel]

=SUMPRODUCT((A1:A9="ramu")*(B1:B9="fail"))

Ctrl&Shift&Enter is not required

[abirami]

Thanks Dave... Its working...
if there are 100 names then I have to change name for each cell?

i.e (A1:A9="ramu') so I need to change ramu name into all the 100 people name?

is there any other short cut for this?

[davesexcel]

...100 names then I have to change name for each cell?

i.e (A1:A9="ramu') so I need to change ramu name into all the 100 people name?

is there any other short cut for this?

Replace "Ramu" with a cell reference such as (A1:A9=C1)

[brokenbiscuits]

If you have a list of everyone's names, say in column C, you could use the formula in column D as such:

=SUMPRODUCT(($A$1:$A$9=C1)*($B$1:$B$9="fail"))

changing the '9' to the last row of data and dragging the formula down.

[DonkeyOte]

It would be best to use a Pivot Table - set Pass/Fail as a Report Filter (set to Fail) set Name as Row Field and Data Field set to Count.

edit: for sake of transparency - attachment added

[mahju]

Provided your data is sorted on "Name" and then on "Result" (Pass/fail) then formula in "C1" will be:

C1:
=IF(AND(A1=A2,B1=B2),"",COUNT(IF(($B$1:B1=B1)*($A$1:A1=A1),($D$1:D1))))

Array formula (Ctrl + Shift + Enter) copied down as you wish
It will add total at each level change as subtotal command

regards

[abirami]

Thanks all for your response.... Now itmakes my job eassy!!!!