[MillB]

Hey all,

It's MillB - and I'm stumped once again.
I have literally wrapped around in circles trying to find this solution.
Can one of you function pros give me a hand?

Here's what I got.
Screenshot 1 - in cell H19 is suppose to be the results of my calculation.
Screenshot 2 - The column I'm trying to calculate.

What I need this to do is count only the columns that have records, and within those only the ones <3. Make sense?

Once I have this it should be easy to replicate for <7 days and <10 days.

Much Thanks!

[MikeTheBike]

Hi

A little confused but would this do it

=SUMPRODUCT((B1:B65535>3)*1)


??


MTB

PS for some bizarre reason the above also counts text entries so I submit this in mitigation !!

=SUMPRODUCT((B1:B65535>3)*ISNUMBER(B1:B65535)*1)

[MillB]

No, that doesn't do it.
The correct amount is currently in the cell H19 in screenshot 1 (81).

This calculation needs to only find the <3 records from B2 to the last record.
However, there's a catch. These records get updated via ODBC from Access, so, the length of the list will change each time it's updated.

Does this make more sense of what I'm trying to accomplish?

Thanks,

MillB

[shades]

You should probably set up a dynamic named range, and use that in your formulas, so that it will always refer to the correct range.

MyRng

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),1)

If B1 is filled then use this:

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)

[MikeTheBike]

Hi

Does this make more sense of what I'm trying to accomplish?

Not really.

If you are trying to COUNT the number of cells in Sheet1, Column B, Row 2 and down, that have a value greater than 3 (?), then this seems to do that

=SUMPRODUCT((Sheet1!B2:B65536>3)*ISNUMBER(Sheet1!B 2:B65536))

If that is not what you want, then perhaps a little more info is required!

However, if that is what you want, and it doesn’t do what I think, perhaps you could indicate what it does do?


MTB