"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.

"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.

"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.

"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

"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

"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!

"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