software

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.

PINRej
New cardApp
NewSuppRej
ActvRej
Re-issueRej
Stol repApp
PINApp
New cardRej
New cardAppresult need
ActionDescriptionCountAppRej
New cardNew card issue3 21
NewSuppNew Supplementary11
PINNew PIN2 11
ActvActivation1 1
Re-issueRe-issue damaged card1 1
Lost repReplace Lost0
Stol repReplace stolen11
Inc LmtLimit increase0
Dec LmtLimit decrease0
Fee revFee reversal0
Act crosscross sell activation0
Canccancel card0
Cont updateContact information update0
Blankempty0
TotTotal9 54

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:
gt; Hi and good day,
gt;
gt; I am trying to find a formula to give me a resolute of the following
gt;
gt; I have two cells in one row one identifies an action and the other
gt; identify if reject or approved, I need to know how many rejects and
gt; approved for each action
gt;
gt; sheet ex.
gt;
gt; PIN Rej
gt; New card App
gt; NewSupp Rej
gt; Actv Rej
gt; Re-issue Rej
gt; Stol rep App
gt; PIN App
gt; New card Rej
gt; New card App
gt;
gt;
gt; result need
gt; Action Description Count App Rej
gt; New card New card issue 3 2 1
gt; NewSupp New Supplementary 1 1
gt; PIN New PIN 2 1 1
gt; Actv Activation 1 1
gt; Re-issue Re-issue damaged card 1 1
gt; Lost rep Replace Lost 0
gt; Stol rep Replace stolen 1 1
gt; Inc Lmt Limit increase 0
gt; Dec Lmt Limit decrease 0
gt; Fee rev Fee reversal 0
gt; Act cross cross sell activation 0
gt; Canc cancel card 0
gt; Cont update Contact information update 0
gt; Blank empty 0
gt; Tot Total 9 5 4
gt;
gt; i really need this formula
gt;
gt; thanks and regards
gt; Loay AlKabbani
Dear Loay Al-Kabbani,

You can get it done by using quot;Countifquot; function in excel.

ex: COUNTIF($A$1:$A$9, A14)

Where quot;$A$1:$A$9quot; the range of cells that you want to count. and quot;A14quot; 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 quot;New Cardquot; 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 quot;Appquot;, you can type

=A1amp;quot;-quot;amp;B1

in C1 cell, i.e., opposite to your actual data quot;PIN Rej quot;. and copy it.

You can use this extra data to count the quot;Appquot; amp; quot;Rejquot;. If you don't want to
display these cells, you can colour them white.

After this you can use the following formula to get quot;Appquot; and in a similar
way quot;Rejquot; also.

A B
C
==================================================
14 New Card 9
COUNTIF($C$1:$C$9,A14amp;quot;-quot;amp;$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.

---------------------------------------------------------------------------------------------
quot;
ABCDE
1PIN Rej A1amp;quot;-quot;amp;B1
2New card App A2amp;quot;-quot;amp;B2
3NewSupp Rej A3amp;quot;-quot;amp;B3
4Actv Rej A4amp;quot;-quot;amp;B4
5Re-issue Rej A5amp;quot;-quot;amp;B5
6Stol rep App A6amp;quot;-quot;amp;B6
7PIN App A7amp;quot;-quot;amp;B7
8New card Rej A8amp;quot;-quot;amp;B8
9New card App A9amp;quot;-quot;amp;B9
10
11
12
13ActionDescriptionCountAppRej
14New cardNew card
issueCOUNTIF($A$1:$A$9,A14)COUNTIF($C$1:$C$9,A14amp;quot;-quot;amp;$D$13)COUNTIF($C$1:$C$9,A14amp;quot;-quot;amp;$E$13)
15NewSuppNew
SupplementaryCOUNTIF($A$1:$A$9,A15)COUNTIF($C$1:$C$9,A15amp;quot;-quot;amp;$D$13)COUNTIF($C$1:$C$9,A15amp;quot;-quot;amp;$E$13)
16PINNew
PINCOUNTIF($A$1:$A$9,A16)COUNTIF($C$1:$C$9,A16amp;quot;-quot;amp;$D$13)COUNTIF($C$1:$C$9,A16amp;quot;-quot;amp;$E$13)
17ActvActivationCOUNTIF($A$1:$A$9,A17)COUNTIF($C$1:$C$9,A17amp;quot;-quot;amp;$D$13)COUNTIF($C$1:$C$9,A17amp;quot;-quot;amp;$E$13)
18Re-issueRe-issue damaged
cardCOUNTIF($A$1:$A$9,A18)COUNTIF($C$1:$C$9,A18amp;quot;-quot;amp;$D$13)COUNTIF($C$1:$C$9,A18amp;quot;-quot;amp;$E$13)
19Lost repReplace
LostCOUNTIF($A$1:$A$9,A19)COUNTIF($C$1:$C$9,A19amp;quot;-quot;amp;$D$13)COUNTIF($C$1:$C$9,A19amp;quot;-quot;amp;$E$13)
20Stol repReplace
stolenCOUNTIF($A$1:$A$9,A20)COUNTIF($C$1:$C$9,A20amp;quot;-quot;amp;$D$13)COUNTIF($C$1:$C$9,A20amp;quot;-quot;amp;$E$13)
21Inc LmtLimit
increaseCOUNTIF($A$1:$A$9,A21)COUNTIF($C$1:$C$9,A21amp;quot;-quot;amp;$D$13)COUNTIF($C$1:$C$9,A21amp;quot;-quot;amp;$E$13)
22Dec LmtLimit
decreaseCOUNTIF($A$1:$A$9,A22)COUNTIF($C$1:$C$9,A22amp;quot;-quot;amp;$D$13)COUNTIF($C$1:$C$9,A22amp;quot;-quot;amp;$E$13)
23Fee revFee
reversalCOUNTIF($A$1:$A$9,A23)COUNTIF($C$1:$C$9,A23amp;quot;-quot;amp;$D$13)COUNTIF($C$1:$C$9,A23amp;quot;-quot;amp;$E$13)
24Act crosscross sell
activationCOUNTIF($A$1:$A$9,A24)COUNTIF($C$1:$C$9,A24amp;quot;-quot;amp;$D$13)COUNTIF($C$1:$C$9,A24amp;quot;-quot;amp;$E$13)
25Canccancel
cardCOUNTIF($A$1:$A$9,A25)COUNTIF($C$1:$C$9,A25amp;quot;-quot;amp;$D$13)COUNTIF($C$1:$C$9,A25amp;quot;-quot;amp;$E$13)
26Cont updateContact information
updateCOUNTIF($A$1:$A$9,A26)COUNTIF($C$1:$C$9,A26amp;quot;-quot;amp;$D$13)COUNTIF($C$1:$C$9,A26amp;quot;-quot;amp;$E$13)
27Blank -
emptyCOUNTIF($A$1:$A$9,A27)COUNTIF($C$1:$C$9,A27amp;quot;-quot;amp;$D$13)COUNTIF($C$1:$C$9,A27amp;quot;-quot;amp;$E$13)
28TotTotalSUM(C14:C27)SUM(D1427)SUM(E14:E27)------------------------------------------------------------------------------------------------

quot;Loay Al-Kabbaniquot; wrote:

gt; Hi and good day,
gt;
gt; I am trying to find a formula to give me a resolute of the following
gt;
gt; I have two cells in one row one identifies an action and the other identify
gt; if reject or approved, I need to know how many rejects and approved for each
gt; action
gt;
gt; sheet ex.
gt;
gt; PINRej
gt; New cardApp
gt; NewSuppRej
gt; ActvRej
gt; Re-issueRej
gt; Stol repApp
gt; PINApp
gt; New cardRej
gt; New cardApp
gt;
gt;
gt; result need
gt; ActionDescriptionCountAppRej
gt; New cardNew card issue3 21
gt; NewSuppNew Supplementary11
gt; PINNew PIN2 11
gt; ActvActivation1 1
gt; Re-issueRe-issue damaged card1 1
gt; Lost repReplace Lost0
gt; Stol repReplace stolen11
gt; Inc LmtLimit increase0
gt; Dec LmtLimit decrease0
gt; Fee revFee reversal0
gt; Act crosscross sell activation0
gt; Canccancel card0
gt; Cont updateContact information update0
gt; Blankempty0
gt; TotTotal9 54
gt;
gt; i really need this formula
gt;
gt; thanks and regards
gt; Loay AlKabbani

Try a pivot table. Label the first column quot;actionsquot; and the second
quot;resultsquot;.

Create the PT using the two columns, including the labels.
Drag quot;actionsquot; into the rows area and then drag quot;resultsquot; into the row area
also. Then drag quot;resultsquot; into the data area and set to quot;count resultsquot;.Don Pistulka

quot;Loay Al-Kabbaniquot; gt; wrote in message
...
gt; Hi and good day,
gt;
gt; I am trying to find a formula to give me a resolute of the following
gt;
gt; I have two cells in one row one identifies an action and the other
gt; identify
gt; if reject or approved, I need to know how many rejects and approved for
gt; each
gt; action
gt;
gt; sheet ex.
gt;
gt; PIN Rej
gt; New card App
gt; NewSupp Rej
gt; Actv Rej
gt; Re-issue Rej
gt; Stol rep App
gt; PIN App
gt; New card Rej
gt; New card App
gt;
gt;
gt; result need
gt; Action Description Count App Rej
gt; New card New card issue 3 2 1
gt; NewSupp New Supplementary 1 1
gt; PIN New PIN 2 1 1
gt; Actv Activation 1 1
gt; Re-issue Re-issue damaged card 1 1
gt; Lost rep Replace Lost 0
gt; Stol rep Replace stolen 1 1
gt; Inc Lmt Limit increase 0
gt; Dec Lmt Limit decrease 0
gt; Fee rev Fee reversal 0
gt; Act cross cross sell activation 0
gt; Canc cancel card 0
gt; Cont update Contact information update 0
gt; Blank empty 0
gt; Tot Total 9 5 4
gt;
gt; i really need this formula
gt;
gt; thanks and regards
gt; Loay AlKabbani