Using CountIf with criteria on another sheet
 

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

Using CountIf with criteria on another sheet
 

--

HTH

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


"Lee Hunter" wrote in message
...
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

Using CountIf with criteria on another sheet
 

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

--

HTH

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


"Lee Hunter" wrote in message
...
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

Using CountIf with criteria on another sheet
 

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" wrote in message
...
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

Using CountIf with criteria on another sheet
 

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

"Lee Hunter" wrote:

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" wrote in message
...
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

Using CountIf with criteria on another sheet
 

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

--

HTH

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


"Lee Hunter" wrote in message
...
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

"Lee Hunter" wrote:

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" wrote in message
...
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

Using CountIf with criteria on another sheet
 

Thanks once again, Bob.

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

Your a gem. Thanks again!

Lee

"Bob Phillips" wrote:

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

--

HTH

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


"Lee Hunter" wrote in message
...
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

"Lee Hunter" wrote:

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" wrote in message
...
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