Formula needed to SUM and COUNT in specific way
 

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

Formula needed to SUM and COUNT in specific way
 

Put this in B2 of Sheet2:

=SUMIF(Sheet1!A:A,A2,Sheet1!B:B)

and this in C2:

=COUNTIF(Sheet1A:A,A2)

Then copy both down as required.

Hope this helps.

Pete

On Feb 23, 10:44*am, 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

Formula needed to SUM and COUNT in specific way
 

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

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

Formula needed to SUM and COUNT in specific way
 

Sum in B2:
=SUMIF(Sheet1!$A:$A,A2,Sheet1!B:B)

Count in C2:
=COUNTIF(Sheet1!A:A,A2)


--
Regards!
Stefi



€žK€� ezt Ã*rta:

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
.

Formula needed to SUM and COUNT in specific way
 

You are welcome! Thanks for the feedback!

Clicking the YES button will be appreciated.

--
Regards!
Stefi



€žK€� ezt Ã*rta:

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
.

Formula needed to SUM and COUNT in specific way
 

you know stefi lot of people told me about pressing YES button but
believe me i post my questions and answers in google discussion groups
and i never seen this YES button anywhere. Can you please more
specific where this YES button is. thanks

Formula needed to SUM and COUNT in specific way
 

The YES button is in the unreliable Microsoft web interface to the
newsgroups, but fortunately you're not using that.
--
David Biddulph


"K" wrote in message
...


you know stefi lot of people told me about pressing YES button but
believe me i post my questions and answers in google discussion groups
and i never seen this YES button anywhere. Can you please more
specific where this YES button is. thanks