Using CountIf with criteria on another sheet

L

Lee Hunter

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
 
B

Bob Phillips

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

--

HTH

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

Lee Hunter

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
 
L

Lee Hunter

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
 
L

Lee Hunter

Thanks once again, Bob.

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

Your a gem. Thanks again!

Lee
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting with multiple matching criteria 12
SUMIF with criteria "<>" & "=" 4
Add up cell from sheet 1 to show on sheet 2 5
Countif, multiple criteria 6
Match Multiple Criteria & Return Numeric Labels across single row 8
using CountIF function or Array? 3
COUNTIFS (the numbers in a column, when subtracted meet a certain criteria) 1
Retrieve unique items with 2 criteria (USING SUMPRODUCT) 5

Top