| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count if ? formula needed! | Excel Discussion (Misc queries) | |||
| formula for current stock count needed | Excel Worksheet Functions | |||
| FORMULA NEEDED TO COUNT DAYS IN EXCEL BETWEEN DATES | Excel Worksheet Functions | |||
| More Help Needed with Count formula | Excel Worksheet Functions | |||
| Need formula to count specific cells in a row | Excel Worksheet Functions | |||
![K[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
