Functions

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

Hi

One way
assuming your data is on sheet 1 incolumns A and B, and your result
table is set up on Sheet2 in cells A1:E14
On sheet2 cell C2
=COUNTIF(Sheet1$A:$A$,A2)
in cell D2
=SUMPRODUCT(--(Sheet1!$A$1:$A$10000=A2),--(Sheet1$B$1:$B$10000=$D$1))
in cell E2
=SUMPRODUCT(--(Sheet1!$A$1:$A$10000=A2),--(Sheet1$B$1:$B$10000=$E$1))

Copy formulae in cells C2:E2 through C3:C14
Change ranges to suit your data, but note that Countif can take whole
columns as arguments, but Sumproduct cannot, you must say from cell to
cell It could be $A$1:$A$65535 if necessary (but not 65536, as that is
the same as whole column).

--
Regards

Roger Govier



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

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(D1427) 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

Try a pivot table. Label the first column "actions" and the second
"results".

Create the PT using the two columns, including the labels.
Drag "actions" into the rows area and then drag "results" into the row area
also. Then drag "results" into the data area and set to "count results".


Don Pistulka

"Loay Al-Kabbani" wrote in message
...
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