Array: Counting multiple values within array

[Trilux_nogo]

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

 

[T. Valko]

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"})*sales)

Biff

 

[Guest]

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]

TRANSPOSE requires array entry.

Biff

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??

 

[Guest]

Ah yes - now I remember. Thanks.

TRANSPOSE requires array entry.

Biff