| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ARRAY NOT COUNTING A CERTAIN ROW | Excel Worksheet Functions | |||
| Searching/Counting an array | Excel Discussion (Misc queries) | |||
| meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
| Use array to return array of values | Excel Worksheet Functions | |||
| Counting with Array Formula | Excel Worksheet Functions | |||
Linear Mode
