Functions
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
|