Help with Nested Range counts

[Hile]

WinXP Excel 2k3
I have a pivot and I'm building a matrix. I want to count how many records
in the pivot fit within the range given in my matrix column.

Matrix!B2:B6 looks like this (formatted as text so it let's me keep the dash
w/o converting to date or anything else):
HC Range
1-10
11-30
31-50
50+

So in Matrix!E3 I want a formula that says something like, countif there are
records in my pivot results range "Area!$F$5:$F$787" that are greater or
equal to the number left of the "-" AND less than or equal to the number
right of the "-" in cell Matrix!B3. Then I want to apply that to the other
cells down to E6 where my range ends. Oh yes I may need to add or delete rows
in my range (Matrix!E3:E6) based on the counts I get from the formula.

Hope that made sense. I know it should use things like COUNTIF, FIND, AND,
etc. but I cannot come up with a syntax that will encompass all four
scenarios. It's hurting my head . Please help.

Hopefully I gave enough information. The reason I want it to grab the
numerical value from the cell is because these numbers may change until I
find optimal ranges for what I'm doing which is build a device profile based
on location size.

 

[Bernie Deitrick]

=COUNTIF(Area!$F$5:$F$787,">=" &
VALUE(LEFT(Matrix!B3,FIND("-",Matrix!B3)-1)))-COUNTIF(Area!$F$5:$F$787,">" &
VALUE(MID(Matrix!B3,FIND("-",Matrix!B3)+1,LEN(Matrix!B3))))

HTH,
Bernie
MS Excel MVP

 

[Hile]

Wow, I'm good at excel, but not that good.

This formula works for all but the "50+" record value which since it's a
small set I know it's supposed to return "26" but it is returning "0" instead.

Great start though. I'll see if I can add another statement that tells it to
count if greater than "Left(Matrix!B6,2)". But if you know how to fix it
quicker than I (most likely) I'll appreciate it if you can post a revised
formula.

Thanks a lot.

 

[Hile]

Nevermind, I just changed "50+" to "50-" and it worked, so I didn't have to
tweak the formula.

Sometimes I amaze myself .