Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Nested Range counts
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. -- Hile |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Nested Range counts
=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" wrote in message ... 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. -- Hile |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Nested Range counts
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 "Bernie Deitrick" wrote: =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" wrote in message ... 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. -- Hile |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Nested Range counts
Nevermind, I just changed "50+" to "50-" and it worked, so I didn't have to
tweak the formula. Sometimes I amaze myself :-). -- Hile "Hile" wrote: 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 "Bernie Deitrick" wrote: =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" wrote in message ... 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. -- Hile |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested if using range names | Excel Worksheet Functions | |||
average daily counts to weekly counts | Excel Discussion (Misc queries) | |||
Selecting date range for counts | Excel Worksheet Functions | |||
nested if for a range of dates | Excel Worksheet Functions | |||
Number counts within Date Range | Excel Worksheet Functions |