Using CountIf with criteria on another sheet

[Guest]

How would I accomplish counting the number of occurrences in a table that
fall between upper and lower limit values on another sheet?

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

 

[Bob Phillips]

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

=sumproduct(--(A1:Z1<=Sheet1!A1),--(A1:Z1>=Sheet1!B1))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

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

 

[Guest]

Oops!

Instead of copy paste, I rentered the formula are it works.

Thanks a million.

I'd still appreciate that reference if you can find one.

Lee

 

[Bob Phillips]

A good reference is http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks once again, Bob.

That is a terrific reference. By Jove, I believe I've got it!

Your a gem. Thanks again!

Lee