Re: Using CountIf with criteria on another sheet

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 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
>
>
>
.

Relevant Pages

  • Re: Programmable Counter
    ... and lower limit, which can be programmed when enable is low. ... upper limit and lower limit? ... limit which is only enabled at the appropriate time when not counting. ...
    (comp.lang.verilog)
  • Re: What if there was no dark matter?
    ...   Bound on the dark matter density in the Solar System ... to see except with really specialized instruments. ... According to the abstract, which is all I could read, this is an upper ... They didn't give a lower limit. ...
    (talk.origins)
  • Re: Creating 2D graphic strip chart with trace wrapping
    ... The dynamic range of some of the data traces is quite large, ... "shoots through" the upper limit, it re-enters from the lower limit. ... somehow possible to reflect the 2D line at the upper and lower bounds, ...
    (comp.graphics.api.opengl)
  • List vector using upper and lower limits
    ... spin buttons that control the upper and lower limit the user desires. ... 'new' data range and an upper value of 3674 would be the end). ...
    (microsoft.public.excel.misc)
  • Re: MS Access Query Case Locking
    ... Query criteria in Access are always case-insensitive unless you specify ... However, you state that the data is in fact all upper case, so surely you ... Unfortunatly i have hit one problem that i can not find the ... The tables are from a ODBC databse from another peice of software. ...
    (microsoft.public.access.formscoding)