Hi,

Counting based on two or more AND criteria:

In 2003:
=SUMPRODUCT(--(C$1:C$7=F1),--(B$1:B$7=G1))

In 2007:
=COUNTIFS(C$1:C$7,F1,B$1:B$7,G1)

Where you want to check B1:B7 for the condition in F1 and C1:C7 for the
condition in G1.

An AND condition is one in which both conditions must be true before the
count occurs.

Other formulas that calculate the same thing in some or all cases:
=SUMPRODUCT((C$1:C$7=F1)*(B$1:B$7=G1))
=COUNT(IF(B$1:B$7&C$1:C$7=F1&G1,))
the second one requires array entry - Shift+Ctrl+Enter instead of Enter.

In your case F1 would have 20 and G1 would have ALPHA. Its always more
flexible to reference cells rather than to hardcode conditions into formulas.



If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Malik Nadeem" wrote:

i have one question is it posible example are as under
A B C D E ......
1 20 ALPHA
2 40 BRAWO
3 40 CHARLE
4 20 BRAWO
5 40 ECHO
6 20 ALFA
7 20 CHARLE

in above how many 20 of ALPHA and how many 40? need result sapret
like
ALPHA 20=2 TIMES
40=0 TIMES

and also i want to know how to hide the same formula in cell but cell will
work no body can veiw and change that formula in resulting cell.

regards
Malik Nadeem
Cell # 00971-50-9122837