Advance function of excel to get sorted data

C

Charlie

Name hit1 hit2 hit3 hit4
a 1 2 4 1
c 2 2 2 2
b 2 2 3 3
a 1 1 2 1
c 1 2 1 2
a 3 1 1 1
b 2 2 2 2

Which advance function of excel can be used to find How many times 'a' is
having '1' in each column(hit1,hit2,hit3,hit4) data?
 
B

Bob Phillips

do you want

=SUMPRODUCT(--($A$1:$A$7="a"),--(B1:B7=1))

and

=SUMPRODUCT(--($A$1:$A$7="a"),--(C1:C7=1))

etc.

or just

=SUMPRODUCT(($A$1:$A$7="a")*(B1:E7=1))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top