Re: Using CountIf with criteria on another ***

Thanks for the reply Bob. I can't get this to produce the correct result.
Only the 4th, 10th, 11th and 14th rows produce non zero values. Those are
the rows where the upper and lower limits are equal and where the data have a
perfect match.

I need it to count the number of values within the range. So the 8th row
should have a value of 4, for example

Here's the data:

0.75 0.95 0.85 0.85 0.90 0.85 0.90 0.85
0.90 0.85 0.85 0.90 0.95 0.90 0.95 1.05
0.55 0.75 0.65 0.70 0.90 0.65 0.60 0.60
0.60 0.75 0.70 0.70 0.65 0.70 0.65 0.65
0.55 0.75 0.80 0.70 0.65 0.70 0.75 0.70
0.70 0.95 0.90 0.85 0.95 0.95 0.85 0.80
1.10 1.15 1.25 1.20 0.95 1.30 1.20 1.00
0.90 0.95 0.95 1.00 1.20 1.00 1.10 1.05
0.95 1.05 1.00 0.75 1.00 0.95 0.95 1.05
0.70 0.85 0.85 0.90 0.80 0.80 0.85 0.90
0.75 0.65 0.80 0.65 0.65 0.75 0.85 0.75
0.95 0.80 0.85 0.90 0.75 0.80 0.90 0.85
0.60 0.70 0.70 0.70 0.80 0.70 0.55 0.70
0.90 0.85 0.80 0.80 0.85 0.75 0.85 0.75
and here's the corresponding criteria:
1 1
0.6 1
0.9 0.9
0.8 0.8
1 1
1 1
0.6 1
0.6 1
1 1
0.8 0.8
0.6 1
0.9 9
0.8 0.8


I really appreciate your effort here. I have tried finding a good
explanation for the Sumproduct components, but with no luck. Can you point
me to a good reference. What, for example, does the "(--(" accomplish?

Lee Hunter


"Bob Phillips" wrote:

> =sumproduct(--(A1:Z1<=Sheet1!A1),--(A1:Z1>=Sheet1!B1))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Lee Hunter" <LeeHunter@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:AA0DCAF5-DBBA-4D39-A48C-85D8A6BB40CA@xxxxxxxxxxxxxxxx
> > How would I accomplish counting the number of occurrences in a table that
> > fall between upper and lower limit values on another ***?
> >
> > as in Countif(A1:Z1,"between sheet1!A1 and sheet1!b1")
> >
> > Where a1:z1 are values to be compared against the upper and lower limit
> and
> > then counted if they meet the criteria
>
>
>
.

Loading