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

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

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

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