Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif within array
I have a dataset similar in structure to the following:
# Date X 1 1/01/06 10 1 1/02/06 14 1 1/03/06 15 2 1/04/06 13 2 1/05/06 11 2 1/06/06 9 2 1/07/06 15 3 1/08/06 13 3 1/09/06 25 3 1/10/06 20 What I need to be able to do is to determine how many datapoints within 1/01/06-1/03/06, 1/04/06-1/07/06, 1/08/06-1/10/06 (yes, there may be more datapoints for one interval than another) have X values associated with them that are, say, less than 12. Thus, I would end up with something like: # <12 1 1 2 2 3 0 Now if all I wanted was an average or a sum, I would use an array function like =AVERAGE(IF($A$1:$A$10=$Z1,C$1:C$10,"")), assuming I have a list of 1, 2, 3 in column Z. The problem I am having is that when I try to incorporate COUNTIF into this, it doesn't seem to work, at least not in any way that I've tried. Any ideas would be a great help! Thanks, Grant |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif within array
Try this:
=SUMPRODUCT(--(A$1:A$10=Z1),--(C$1:C$10<12)) Biff "Grant" wrote in message ... I have a dataset similar in structure to the following: # Date X 1 1/01/06 10 1 1/02/06 14 1 1/03/06 15 2 1/04/06 13 2 1/05/06 11 2 1/06/06 9 2 1/07/06 15 3 1/08/06 13 3 1/09/06 25 3 1/10/06 20 What I need to be able to do is to determine how many datapoints within 1/01/06-1/03/06, 1/04/06-1/07/06, 1/08/06-1/10/06 (yes, there may be more datapoints for one interval than another) have X values associated with them that are, say, less than 12. Thus, I would end up with something like: # <12 1 1 2 2 3 0 Now if all I wanted was an average or a sum, I would use an array function like =AVERAGE(IF($A$1:$A$10=$Z1,C$1:C$10,"")), assuming I have a list of 1, 2, 3 in column Z. The problem I am having is that when I try to incorporate COUNTIF into this, it doesn't seem to work, at least not in any way that I've tried. Any ideas would be a great help! Thanks, Grant |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif within array
Thanks, Biff. That did the trick. The only thing I was wondering -- what
precisely do the "--" do in the formula? Thanks again, Grant "Biff" wrote: Try this: =SUMPRODUCT(--(A$1:A$10=Z1),--(C$1:C$10<12)) Biff "Grant" wrote in message ... I have a dataset similar in structure to the following: # Date X 1 1/01/06 10 1 1/02/06 14 1 1/03/06 15 2 1/04/06 13 2 1/05/06 11 2 1/06/06 9 2 1/07/06 15 3 1/08/06 13 3 1/09/06 25 3 1/10/06 20 What I need to be able to do is to determine how many datapoints within 1/01/06-1/03/06, 1/04/06-1/07/06, 1/08/06-1/10/06 (yes, there may be more datapoints for one interval than another) have X values associated with them that are, say, less than 12. Thus, I would end up with something like: # <12 1 1 2 2 3 0 Now if all I wanted was an average or a sum, I would use an array function like =AVERAGE(IF($A$1:$A$10=$Z1,C$1:C$10,"")), assuming I have a list of 1, 2, 3 in column Z. The problem I am having is that when I try to incorporate COUNTIF into this, it doesn't seem to work, at least not in any way that I've tried. Any ideas would be a great help! Thanks, Grant |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif within array
You're welcome. Thanks for the feedback!
what precisely do the "--" do in the formula? See this: http://xldynamic.com/source/xld.SUMPRODUCT.html Biff "Grant" wrote in message ... Thanks, Biff. That did the trick. The only thing I was wondering -- what precisely do the "--" do in the formula? Thanks again, Grant "Biff" wrote: Try this: =SUMPRODUCT(--(A$1:A$10=Z1),--(C$1:C$10<12)) Biff "Grant" wrote in message ... I have a dataset similar in structure to the following: # Date X 1 1/01/06 10 1 1/02/06 14 1 1/03/06 15 2 1/04/06 13 2 1/05/06 11 2 1/06/06 9 2 1/07/06 15 3 1/08/06 13 3 1/09/06 25 3 1/10/06 20 What I need to be able to do is to determine how many datapoints within 1/01/06-1/03/06, 1/04/06-1/07/06, 1/08/06-1/10/06 (yes, there may be more datapoints for one interval than another) have X values associated with them that are, say, less than 12. Thus, I would end up with something like: # <12 1 1 2 2 3 0 Now if all I wanted was an average or a sum, I would use an array function like =AVERAGE(IF($A$1:$A$10=$Z1,C$1:C$10,"")), assuming I have a list of 1, 2, 3 in column Z. The problem I am having is that when I try to incorporate COUNTIF into this, it doesn't seem to work, at least not in any way that I've tried. Any ideas would be a great help! Thanks, Grant |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif array formula | Excel Worksheet Functions | |||
Array formula using two columns and countif | Excel Worksheet Functions | |||
Complex countif of array elements | Excel Worksheet Functions | |||
I need to create an array formula combined with a countif | Excel Worksheet Functions | |||
An array and countif based on criteria in each column | Excel Worksheet Functions |