Formula needed to SUM and COUNT in specific way
On Feb 23, 11:23*am, Andrew Taylor wrote:
You need SUMIF() and COUNTIF()
In cell B2 of Sheet2: =SUMIF(Sheet1!$A$2:$A$100,A2,Sheet1!$B$2:$B$100)
In cell C2 of Sheet2: =COUNTIF(Sheet1!$A$2:$A$100,A2)
then copy down..
(assumes data in Sheet1 doesn't extend below row 100)
hth
Andrew
On 23 Feb, 10:44, K wrote:
Hi all, *I have data in Sheet1 and Sheet2 as shown below
Sheet1
* *A * * * * * *B-----col
Data * *Amount----headings
XX * * * * * *2
YY * * * * * 4
SS * * * * * 5
XX * * * * * *9
GG * * * * *8
HH * * * * * 3
SS * * * * * 5
Sheet2
* A * * * * *B * * * * * C---col
Data * * Sum * * Count---headings
XX
SS
HH
I need some kind of SUM formula in column B and COUNT formula in
column C of Sheet2 which should only SUM and COUNT the amounts in
column B of Sheet1 of which same row value in column A of Sheet 1
match with column A of Sheet2. *So the result should be like as shown
below.
Sheet2
* A * * * * *B * * * * * C---col
Data * * Sum * * Count---heading
XX * * * * *11 * * * * *2
SS * * * * 10 * * * * *2
HH * * * * * 3 * * * * *1
Please can any friend have any formula in mind for this kind of
calculation. *Thanks in advance- Hide quoted text -
- Show quoted text -
Thanks guys
|