Functions
Dear Loay Al-Kabbani,
You can get it done by using "Countif" function in excel.
ex: COUNTIF($A$1:$A$9, A14)
Where "$A$1:$A$9" the range of cells that you want to count. and "A14" is
the cell which you want to count. In your example you can do it like this:
A B
===============
PIN Rej
New card App
NewSupp Rej
Actv Rej
Re-issue Rej
Stol rep App
PIN App
New card Rej
New card App
=================
Type "New Card" in A14
A B
====================================
14 New Card =COUNTIF($A$1:$A$9, A14)
15 NewSupp
16 PIN
17 Actv
18 Re-issue
19 Lost rep
20 Stol rep
21 Inc Lmt
22 Dec Lmt
23 Fee rev
24 Act cross
25 Canc
26 Cont update
======================================
You can copy B14 to other cells bellow it.
And for getting "App", you can type
=A1&"-"&B1
in C1 cell, i.e., opposite to your actual data "PIN Rej ". and copy it.
You can use this extra data to count the "App" & "Rej". If you don't want to
display these cells, you can colour them white.
After this you can use the following formula to get "App" and in a similar
way "Rej" also.
A B
C
==================================================
14 New Card 9
COUNTIF($C$1:$C$9,A14&"-"&$D$13)
15 NewSupp
16 PIN
17 Actv
18 Re-issue
19 Lost rep
20 Stol rep
21 Inc Lmt
22 Dec Lmt
23 Fee rev
24 Act cross
25 Canc
26 Cont update
======================================
OK.
Bye
NAVEEN.
---------------------------------------------------------------------------------------------
"
A B C D E
1 PIN Rej A1&"-"&B1
2 New card App A2&"-"&B2
3 NewSupp Rej A3&"-"&B3
4 Actv Rej A4&"-"&B4
5 Re-issue Rej A5&"-"&B5
6 Stol rep App A6&"-"&B6
7 PIN App A7&"-"&B7
8 New card Rej A8&"-"&B8
9 New card App A9&"-"&B9
10
11
12
13 Action Description Count App Rej
14 New card New card
issue COUNTIF($A$1:$A$9,A14) COUNTIF($C$1:$C$9,A14&"-"&$D$13) COUNTIF($C$1:$C$9,A14&"-"&$E$13)
15 NewSupp New
Supplementary COUNTIF($A$1:$A$9,A15) COUNTIF($C$1:$C$9,A15&"-"&$D$13) COUNTIF($C$1:$C$9,A15&"-"&$E$13)
16 PIN New
PIN COUNTIF($A$1:$A$9,A16) COUNTIF($C$1:$C$9,A16&"-"&$D$13) COUNTIF($C$1:$C$9,A16&"-"&$E$13)
17 Actv Activation COUNTIF($A$1:$A$9,A17) COUNTIF($C$1:$C$9,A17&"-"&$D$13) COUNTIF($C$1:$C$9,A17&"-"&$E$13)
18 Re-issue Re-issue damaged
card COUNTIF($A$1:$A$9,A18) COUNTIF($C$1:$C$9,A18&"-"&$D$13) COUNTIF($C$1:$C$9,A18&"-"&$E$13)
19 Lost rep Replace
Lost COUNTIF($A$1:$A$9,A19) COUNTIF($C$1:$C$9,A19&"-"&$D$13) COUNTIF($C$1:$C$9,A19&"-"&$E$13)
20 Stol rep Replace
stolen COUNTIF($A$1:$A$9,A20) COUNTIF($C$1:$C$9,A20&"-"&$D$13) COUNTIF($C$1:$C$9,A20&"-"&$E$13)
21 Inc Lmt Limit
increase COUNTIF($A$1:$A$9,A21) COUNTIF($C$1:$C$9,A21&"-"&$D$13) COUNTIF($C$1:$C$9,A21&"-"&$E$13)
22 Dec Lmt Limit
decrease COUNTIF($A$1:$A$9,A22) COUNTIF($C$1:$C$9,A22&"-"&$D$13) COUNTIF($C$1:$C$9,A22&"-"&$E$13)
23 Fee rev Fee
reversal COUNTIF($A$1:$A$9,A23) COUNTIF($C$1:$C$9,A23&"-"&$D$13) COUNTIF($C$1:$C$9,A23&"-"&$E$13)
24 Act cross cross sell
activation COUNTIF($A$1:$A$9,A24) COUNTIF($C$1:$C$9,A24&"-"&$D$13) COUNTIF($C$1:$C$9,A24&"-"&$E$13)
25 Canc cancel
card COUNTIF($A$1:$A$9,A25) COUNTIF($C$1:$C$9,A25&"-"&$D$13) COUNTIF($C$1:$C$9,A25&"-"&$E$13)
26 Cont update Contact information
update COUNTIF($A$1:$A$9,A26) COUNTIF($C$1:$C$9,A26&"-"&$D$13) COUNTIF($C$1:$C$9,A26&"-"&$E$13)
27 Blank -
empty COUNTIF($A$1:$A$9,A27) COUNTIF($C$1:$C$9,A27&"-"&$D$13) COUNTIF($C$1:$C$9,A27&"-"&$E$13)
28 Tot Total SUM(C14:C27) SUM(D14:D27) SUM(E14:E27)
------------------------------------------------------------------------------------------------
"Loay Al-Kabbani" wrote:
Hi and good day,
I am trying to find a formula to give me a resolute of the following
I have two cells in one row one identifies an action and the other identify
if reject or approved, I need to know how many rejects and approved for each
action
sheet ex.
PIN Rej
New card App
NewSupp Rej
Actv Rej
Re-issue Rej
Stol rep App
PIN App
New card Rej
New card App
result need
Action Description Count App Rej
New card New card issue 3 2 1
NewSupp New Supplementary 1 1
PIN New PIN 2 1 1
Actv Activation 1 1
Re-issue Re-issue damaged card 1 1
Lost rep Replace Lost 0
Stol rep Replace stolen 1 1
Inc Lmt Limit increase 0
Dec Lmt Limit decrease 0
Fee rev Fee reversal 0
Act cross cross sell activation 0
Canc cancel card 0
Cont update Contact information update 0
Blank empty 0
Tot Total 9 5 4
i really need this formula
thanks and regards
Loay AlKabbani
|