A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formula needed to SUM and COUNT in specific way



 
 
Thread Tools Display Modes
  #1  
Old February 23rd, 2010, 11:44 AM posted to microsoft.public.excel.misc
K[_4_]
external usenet poster
 
Posts: 25
Default 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
  #2  
Old February 23rd, 2010, 12:22 PM posted to microsoft.public.excel.misc
Pete_UK
external usenet poster
 
Posts: 8,780
Default 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


  #3  
Old February 23rd, 2010, 12:23 PM posted to microsoft.public.excel.misc
Andrew Taylor
external usenet poster
 
Posts: 18
Default 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


  #4  
Old February 23rd, 2010, 12:35 PM posted to microsoft.public.excel.misc
K[_4_]
external usenet poster
 
Posts: 25
Default 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
  #5  
Old February 23rd, 2010, 12:40 PM posted to microsoft.public.excel.misc
Stefi
external usenet poster
 
Posts: 1,841
Default 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
.

  #6  
Old February 25th, 2010, 09:39 AM posted to microsoft.public.excel.misc
Stefi
external usenet poster
 
Posts: 1,841
Default 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
.

  #7  
Old February 28th, 2010, 11:03 AM posted to microsoft.public.excel.misc
K[_4_]
external usenet poster
 
Posts: 25
Default 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
  #8  
Old February 28th, 2010, 02:27 PM posted to microsoft.public.excel.misc
David Biddulph
external usenet poster
 
Posts: 8,714
Default 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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:04 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.