Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count & Array Function
I have the following Dataset
Month Region Amount Jan East 10 Jan East 7 Jan West 5 Feb South 2 Feb South 1 I am trying to write an array function that will count the number of sales in January in the East Region. I have sucessfully been able to write the array function to sum East in January but just found out that the Count function does not accept arrays. Any help would be appreciated. Thanks, Scott |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count & Array Function
Try:
=SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100="East"),(C2:C100)) If dates are actual dates e.g 01/01/07 formatted as "mmm", not text then for January: =SUMPRODUCT(--MONTH(A2:A100)=1),--(B2:B100="East"),(C2:C100)) Equally, you can replace the constants e.g 1(or "Jan") and "East" with cells containing these values. FYI the "--" changes the TRUE/FALSE conditions to 1/0 so we can do the arithmetic. HTH "Scott Halper" wrote: I have the following Dataset Month Region Amount Jan East 10 Jan East 7 Jan West 5 Feb South 2 Feb South 1 I am trying to write an array function that will count the number of sales in January in the East Region. I have sucessfully been able to write the array function to sum East in January but just found out that the Count function does not accept arrays. Any help would be appreciated. Thanks, Scott |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count & Array Function
On Mar 23, 1:04 pm, Toppers wrote:
Try: =SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100="East"),(C2:C100)) If dates are actual dates e.g 01/01/07 formatted as "mmm", not text then for January: =SUMPRODUCT(--MONTH(A2:A100)=1),--(B2:B100="East"),(C2:C100)) Equally, you can replace the constants e.g 1(or "Jan") and "East" with cells containing these values. FYI the "--" changes the TRUE/FALSE conditions to 1/0 so we can do the arithmetic. HTH "Scott Halper" wrote: I have the following Dataset Month Region Amount Jan East 10 Jan East 7 Jan West 5 Feb South 2 Feb South 1 I am trying to write an array function that will count the number of sales in January in the East Region. I have sucessfully been able to write the array function to sum East in January but just found out that the Count function does not accept arrays. Any help would be appreciated. Thanks, Scott- Hide quoted text - - Show quoted text - Thanks, but when I tried your formula, the result is 17 which is the sum of the two sales in January and I am looking for the number 2. Any help on that? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count & Array Function
remove the (c2:c100) from the formula.
Toppers thought that you wanted the Amount field summed--not the count of sales. Scott Halper wrote: On Mar 23, 1:04 pm, Toppers wrote: Try: =SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100="East"),(C2:C100)) If dates are actual dates e.g 01/01/07 formatted as "mmm", not text then for January: =SUMPRODUCT(--MONTH(A2:A100)=1),--(B2:B100="East"),(C2:C100)) Equally, you can replace the constants e.g 1(or "Jan") and "East" with cells containing these values. FYI the "--" changes the TRUE/FALSE conditions to 1/0 so we can do the arithmetic. HTH "Scott Halper" wrote: I have the following Dataset Month Region Amount Jan East 10 Jan East 7 Jan West 5 Feb South 2 Feb South 1 I am trying to write an array function that will count the number of sales in January in the East Region. I have sucessfully been able to write the array function to sum East in January but just found out that the Count function does not accept arrays. Any help would be appreciated. Thanks, Scott- Hide quoted text - - Show quoted text - Thanks, but when I tried your formula, the result is 17 which is the sum of the two sales in January and I am looking for the number 2. Any help on that? -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count & Array Function
=SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100="East"))
"Scott Halper" wrote: On Mar 23, 1:04 pm, Toppers wrote: Try: =SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100="East"),(C2:C100)) If dates are actual dates e.g 01/01/07 formatted as "mmm", not text then for January: =SUMPRODUCT(--MONTH(A2:A100)=1),--(B2:B100="East"),(C2:C100)) Equally, you can replace the constants e.g 1(or "Jan") and "East" with cells containing these values. FYI the "--" changes the TRUE/FALSE conditions to 1/0 so we can do the arithmetic. HTH "Scott Halper" wrote: I have the following Dataset Month Region Amount Jan East 10 Jan East 7 Jan West 5 Feb South 2 Feb South 1 I am trying to write an array function that will count the number of sales in January in the East Region. I have sucessfully been able to write the array function to sum East in January but just found out that the Count function does not accept arrays. Any help would be appreciated. Thanks, Scott- Hide quoted text - - Show quoted text - Thanks, but when I tried your formula, the result is 17 which is the sum of the two sales in January and I am looking for the number 2. Any help on that? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inconsistent Array Count results | Excel Worksheet Functions | |||
Count If Array Formula | Excel Worksheet Functions | |||
Count if array contains cells of a certain value | Excel Worksheet Functions | |||
How can you use count with an array formula similar to using sum | Excel Worksheet Functions | |||
Can I count in an array based on a function? | Excel Worksheet Functions |