Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA NEEDS OF COUNTING
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA NEEDS OF COUNTING
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 "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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA NEEDS OF COUNTING
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 On Oct 19, 2:02*pm, 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA NEEDS OF COUNTING
THANKS A LOT MUDDAN
"muddan madhu" wrote: 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 On Oct 19, 2:02 pm, 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA NEEDS OF COUNTING
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Formula | Excel Worksheet Functions | |||
Counting formula | Excel Discussion (Misc queries) | |||
Counting formula | Excel Discussion (Misc queries) | |||
Counting formula | Excel Worksheet Functions | |||
formula on counting | Excel Discussion (Misc queries) |