FORMULA NEEDS OF COUNTING

[Malik Nadeem]

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

 

[Mike H]

Hi,

In C1
=A1 & " = "&SUMPRODUCT(($B$1:$B$7="Alpha")*($A$1:$A$7=20))&" Times"

in c2
=A2 & " = "&SUMPRODUCT(($B$1:$B$7="Alpha")*($A$1:$A$7=40))&" Times"

I've assumed the differences in spelling of Alpha are typos

Mike

 

[muddan madhu]

try this

in C1 put this formula ( use ctrl + shift + enter )
=COUNT(IF(A1:A7&B1:B7=20&"ALPHA",))

in C2 put this formula ( use ctrl + shift + enter )
=COUNT(IF(A1:A7&B1:B7=40&"ALPHA",))


to hide the formula displayed in formula bar
Select the sheet ( Ctrl + A ) | go to format | cells | protection tab
| uncheck both locked & hidden |
now select the cell which needs to hide the formula | go to format |
cells | protection tab | check both locked & hidden | ok |
now go to tools | protection | protect sheet ( password optional | ok

 

[Malik Nadeem]

THANKS A LOT MUDDAN

try this

in C1 put this formula ( use ctrl + shift + enter )
=COUNT(IF(A1:A7&B1:B7=20&"ALPHA",))

in C2 put this formula ( use ctrl + shift + enter )
=COUNT(IF(A1:A7&B1:B7=40&"ALPHA",))


to hide the formula displayed in formula bar
Select the sheet ( Ctrl + A ) | go to format | cells | protection tab
| uncheck both locked & hidden |
now select the cell which needs to hide the formula | go to format |
cells | protection tab | check both locked & hidden | ok |
now go to tools | protection | protect sheet ( password optional | ok

 

[ShaneDevenshire]

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.