RENEWED-- Arrays: Counting multiple values within array

[Trilux_nogo]

Thanks to T. Valko and JMB who answered my prior question on the same
subject but I'm afraid I left out a couple of significant details.

My renewed question. I'm using Excel 2003 and know how to use arrays--up to
a point.

I have several columnar ranges in a worksheet called "Data" containing Item,
Month, Country, and Sales for some 10K lines. All are named ranges.

On top of a second tab, I have two dropdown lists. One with the months
(pointing to cell A1) and the second with the countries (pointing to cell
A2) The object is to enable anyone to see the total sales for a specific tem
in any month and any county.

So, I have on my column A (starting at A5) a list of items such as

Gizmos
Widgets
Klunks
Whazzits

And right next to each an array

{=SUM((Item=$A5)*(Month=$A$1)*(Country=$A$2)*Sales)} and so on down the
list.

It works just fine if the dropdowns 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
listed under Countries as "BU" and "TI"
How do I tell the array to add up the sales in both when I put BUTI (or
anything else) in cell A2?????? Or anywhere else for that matter?

The trick here is to have the array give me the result for a specific item
for one country if I select that and the total for more than one if I select
the Total of two in the dropdown.

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

In essence, how do I tell an array to add up TWO (or more) entries in some
cases??

Any ideas?

TIA

 

[JMB]

Assuming your multi-country codes are all 4 characters in length, perhaps:

=SUM((item=$A5)*(month=$A$1)*(((country=LEFT($A$2,2))+(country=RIGHT($A$2,2)))>0)*Sales)

 

[JMB]

Or - just assuming your country codes are all two characters, maybe

=SUM((item=$A5)*(month=$A$1)*(country=TRANSPOSE(MID($A$2,ROW(INDIRECT("1:"&LEN($A$2)/2))*2-1,2)))*Sales)

From playing around, it appears this would pick up "BUTI" or "BUTIUS"
(Burundi and Timbuctu and United States), etc.

Array entered, BTW.

 

[Trilux_nogo]

Thanks, I'll give it a try.

And, no. The codes don't have to be in a single cell. But I'm having trouble
having it calculate something like (Country="BU") + (Country="TI") ecc...

 

[Trilux_nogo]

Tried it and worked like a charm! Don't quite completely understand it, but
will figure it out.

Thanks a mil, you're a genius!

 

[JMB]

You're quite welcome.

Tried it and worked like a charm! Don't quite completely understand it, but
will figure it out.

Thanks a mil, you're a genius!