Formula needed to SUM and COUNT in specific way

[K]

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

 

[Pete_UK]

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

 

[Andrew Taylor]

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

 

[K]

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








- Show quoted text -

Thanks guys

 

[Stefi]

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

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


--
Regards!
Stefi



„K†ezt írta:

 

[Stefi]

You are welcome! Thanks for the feedback!

Clicking the YES button will be appreciated.

--
Regards!
Stefi



„K†ezt írta:

 

[K]

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

 

[David Biddulph]

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