Array: Counting multiple values within array
 

To begin with, thanks to all those who answer the million questions posted
here. Amazing the quantity of different problems people come up with and
astonishing the amount of time several devote to solving them. My hat off to
you.

OK... my question, but first, I'm using Excel 2003 and know how to use
arrays--up to a point.

I have several columnar ranges in a worksheet called, say, Month, Country,
Sales. All are named ranges.
So, my array {=SUM((Month=$A$1)*(Country=$A$2)*Sales)} works just fine if I
put the month (2 for February) and the Country (US for USA) in the
respective cells.

But let's say I need the total for TWO countries, like Burundi and Timbuctu.
How do I tell the array to add up the sales in both when I put BUTI in cell
A2??????

I've tried everything, like entering the array for each country in separate
lines and naming "BUTI" the total.

Still no go. Any ideas?

TIA

Array: Counting multiple values within array
 

Try one of these (normally entered, not array entered formulas):

A1 = month number
A2 = US
A3 = BUTI

=SUMPRODUCT(--(month=A1),--(ISNUMBER(MATCH(country,A2:A3,0))),sales)

Or, hardcoded:

=SUMPRODUCT((month=A1)*(country={"US","BUTI"})*sal es)

Biff

"Trilux_nogo" wrote in message
...
To begin with, thanks to all those who answer the million questions posted
here. Amazing the quantity of different problems people come up with and
astonishing the amount of time several devote to solving them. My hat off
to you.

OK... my question, but first, I'm using Excel 2003 and know how to use
arrays--up to a point.

I have several columnar ranges in a worksheet called, say, Month, Country,
Sales. All are named ranges.
So, my array {=SUM((Month=$A$1)*(Country=$A$2)*Sales)} works just fine if
I put the month (2 for February) and the Country (US for USA) in the
respective cells.

But let's say I need the total for TWO countries, like Burundi and
Timbuctu. How do I tell the array to add up the sales in both when I put
BUTI in cell A2??????

I've tried everything, like entering the array for each country in
separate lines and naming "BUTI" the total.

Still no go. Any ideas?

TIA

Array: Counting multiple values within array
 

This also appeared to work (array entered):
=SUM((month=A1)*(country=TRANSPOSE(A2:A3))*sales)

It looks to me like it should behave similar to using the array constant
{"US", "BUTI"}, but I'm unable to enter the formula normally w/Sumproduct
unless I use CSE. Do you know why that is??


"T. Valko" wrote:

Try one of these (normally entered, not array entered formulas):

A1 = month number
A2 = US
A3 = BUTI

=SUMPRODUCT(--(month=A1),--(ISNUMBER(MATCH(country,A2:A3,0))),sales)

Or, hardcoded:

=SUMPRODUCT((month=A1)*(country={"US","BUTI"})*sal es)

Biff

"Trilux_nogo" wrote in message
...
To begin with, thanks to all those who answer the million questions posted
here. Amazing the quantity of different problems people come up with and
astonishing the amount of time several devote to solving them. My hat off
to you.

OK... my question, but first, I'm using Excel 2003 and know how to use
arrays--up to a point.

I have several columnar ranges in a worksheet called, say, Month, Country,
Sales. All are named ranges.
So, my array {=SUM((Month=$A$1)*(Country=$A$2)*Sales)} works just fine if
I put the month (2 for February) and the Country (US for USA) in the
respective cells.

But let's say I need the total for TWO countries, like Burundi and
Timbuctu. How do I tell the array to add up the sales in both when I put
BUTI in cell A2??????

I've tried everything, like entering the array for each country in
separate lines and naming "BUTI" the total.

Still no go. Any ideas?

TIA

Array: Counting multiple values within array
 

TRANSPOSE requires array entry.

Biff

"JMB" wrote in message
...
This also appeared to work (array entered):
=SUM((month=A1)*(country=TRANSPOSE(A2:A3))*sales)

It looks to me like it should behave similar to using the array constant
{"US", "BUTI"}, but I'm unable to enter the formula normally w/Sumproduct
unless I use CSE. Do you know why that is??


"T. Valko" wrote:

Try one of these (normally entered, not array entered formulas):

A1 = month number
A2 = US
A3 = BUTI

=SUMPRODUCT(--(month=A1),--(ISNUMBER(MATCH(country,A2:A3,0))),sales)

Or, hardcoded:

=SUMPRODUCT((month=A1)*(country={"US","BUTI"})*sal es)

Biff

"Trilux_nogo" wrote in message
...
To begin with, thanks to all those who answer the million questions
posted
here. Amazing the quantity of different problems people come up with
and
astonishing the amount of time several devote to solving them. My hat
off
to you.

OK... my question, but first, I'm using Excel 2003 and know how to use
arrays--up to a point.

I have several columnar ranges in a worksheet called, say, Month,
Country,
Sales. All are named ranges.
So, my array {=SUM((Month=$A$1)*(Country=$A$2)*Sales)} works just fine
if
I put the month (2 for February) and the Country (US for USA) in the
respective cells.

But let's say I need the total for TWO countries, like Burundi and
Timbuctu. How do I tell the array to add up the sales in both when I
put
BUTI in cell A2??????

I've tried everything, like entering the array for each country in
separate lines and naming "BUTI" the total.

Still no go. Any ideas?

TIA

Array: Counting multiple values within array
 

Ah yes - now I remember. Thanks.

"T. Valko" wrote:

TRANSPOSE requires array entry.

Biff

"JMB" wrote in message
...
This also appeared to work (array entered):
=SUM((month=A1)*(country=TRANSPOSE(A2:A3))*sales)

It looks to me like it should behave similar to using the array constant
{"US", "BUTI"}, but I'm unable to enter the formula normally w/Sumproduct
unless I use CSE. Do you know why that is??


"T. Valko" wrote:

Try one of these (normally entered, not array entered formulas):

A1 = month number
A2 = US
A3 = BUTI

=SUMPRODUCT(--(month=A1),--(ISNUMBER(MATCH(country,A2:A3,0))),sales)

Or, hardcoded:

=SUMPRODUCT((month=A1)*(country={"US","BUTI"})*sal es)

Biff

"Trilux_nogo" wrote in message
...
To begin with, thanks to all those who answer the million questions
posted
here. Amazing the quantity of different problems people come up with
and
astonishing the amount of time several devote to solving them. My hat
off
to you.

OK... my question, but first, I'm using Excel 2003 and know how to use
arrays--up to a point.

I have several columnar ranges in a worksheet called, say, Month,
Country,
Sales. All are named ranges.
So, my array {=SUM((Month=$A$1)*(Country=$A$2)*Sales)} works just fine
if
I put the month (2 for February) and the Country (US for USA) in the
respective cells.

But let's say I need the total for TWO countries, like Burundi and
Timbuctu. How do I tell the array to add up the sales in both when I
put
BUTI in cell A2??????

I've tried everything, like entering the array for each country in
separate lines and naming "BUTI" the total.

Still no go. Any ideas?

TIA