Array: Counting multiple values within array

T

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

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
 
J

JMB

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

T. Valko

TRANSPOSE requires array entry.

Biff

JMB said:
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??
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top