Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Sumif" on multiple values
Hi all, I feel a little sheepish posting this question, but I really can't
get this to work: I have a list of customer accounts in Sheet 1 column A, with sales turnover in column C. Some of those accounts belong to the same group of companies. I have the list of account numbers in, say Sheet 2 A1:A10. How do I sum the sales turnover of the accounts in Sheet 1 that appear on the list of account numbers in sheet 2? I've tried sumif and sum if as array functions, but I can't quite find the right formula to use. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Sumif" on multiple values
In Sheet2
Put in B1: =SUMIF(Sheet1!A:A,A1,Sheet1!C:C) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tim Green" wrote: Hi all, I feel a little sheepish posting this question, but I really can't get this to work: I have a list of customer accounts in Sheet 1 column A, with sales turnover in column C. Some of those accounts belong to the same group of companies. I have the list of account numbers in, say Sheet 2 A1:A10. How do I sum the sales turnover of the accounts in Sheet 1 that appear on the list of account numbers in sheet 2? I've tried sumif and sum if as array functions, but I can't quite find the right formula to use. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Sumif" on multiple values
Hi Max,
Sorry if I wasn't clear, let me explain: my output needs to be something like: Company Turnover (£000s) Group X 120 Group Y 15 Company A 200 ....where the 120 is a sum of all 10 accounts belonging to group X. The list of accounts belonging to group X is the array 'Sheet 2'!A1:A10. I hope that makes sense. Thanks "Max" wrote: In Sheet2 Put in B1: =SUMIF(Sheet1!A:A,A1,Sheet1!C:C) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tim Green" wrote: Hi all, I feel a little sheepish posting this question, but I really can't get this to work: I have a list of customer accounts in Sheet 1 column A, with sales turnover in column C. Some of those accounts belong to the same group of companies. I have the list of account numbers in, say Sheet 2 A1:A10. How do I sum the sales turnover of the accounts in Sheet 1 that appear on the list of account numbers in sheet 2? I've tried sumif and sum if as array functions, but I can't quite find the right formula to use. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Sumif" on multiple values
In Sheet2
Put in B1: =SUMPRODUCT(--ISNUMBER(MATCH(Sheet1!A2:A100,A1:A10,0)),Sheet1!C2 :C100) Adapt the Sheet1 ranges to suit your actual -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tim Green" wrote: Hi Max, Sorry if I wasn't clear, let me explain: my output needs to be something like: Company Turnover (£000s) Group X 120 Group Y 15 Company A 200 ...where the 120 is a sum of all 10 accounts belonging to group X. The list of accounts belonging to group X is the array 'Sheet 2'!A1:A10. I hope that makes sense. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Sumif" on multiple values
Thank you, that's exactly what I was looking for. I've never used sumproduct
before, I'll have to look into it! "Max" wrote: In Sheet2 Put in B1: =SUMPRODUCT(--ISNUMBER(MATCH(Sheet1!A2:A100,A1:A10,0)),Sheet1!C2 :C100) Adapt the Sheet1 ranges to suit your actual -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tim Green" wrote: Hi Max, Sorry if I wasn't clear, let me explain: my output needs to be something like: Company Turnover (£000s) Group X 120 Group Y 15 Company A 200 ...where the 120 is a sum of all 10 accounts belonging to group X. The list of accounts belonging to group X is the array 'Sheet 2'!A1:A10. I hope that makes sense. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Sumif" on multiple values
welcome, Tim.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tim Green" wrote in message ... Thank you, that's exactly what I was looking for. I've never used sumproduct before, I'll have to look into it! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Sumif" on multiple values
That "--" trick to turn a logical value into a number is also news to me, so
thank you for educating and informing me! "Max" wrote: In Sheet2 Put in B1: =SUMPRODUCT(--ISNUMBER(MATCH(Sheet1!A2:A100,A1:A10,0)),Sheet1!C2 :C100) Adapt the Sheet1 ranges to suit your actual -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tim Green" wrote: Hi Max, Sorry if I wasn't clear, let me explain: my output needs to be something like: Company Turnover (£000s) Group X 120 Group Y 15 Company A 200 ...where the 120 is a sum of all 10 accounts belonging to group X. The list of accounts belonging to group X is the array 'Sheet 2'!A1:A10. I hope that makes sense. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I test values in 2 different columns in a "sumif" formula? | Excel Discussion (Misc queries) | |||
"sumif" With Multiple Criteria! | Excel Worksheet Functions | |||
help needed on "sumif function with multiple ifs" | Excel Discussion (Misc queries) | |||
Can I apply a "score" based on multiple cell values? | Excel Worksheet Functions | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |