1. Registered User
Join Date
Dec 2006
Posts
39

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!

2. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
804
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)
Last edited by MikeTheBike; 05-29-08 at 08:15.

3. Registered User
Join Date
Dec 2006
Posts
39

## Hey Mike

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

4. Registered User
Join Date
Oct 2003
Posts
1,091
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)

5. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
804
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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•