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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|