Countif + Named range
I'm using a simple Countif in XL2003 so don't need to CSE it. It fights with
a named range and I cannot see why. It worked when I set it up, but the moment I changed anything it stopped. Where am I going wrong? Given a 50-cell range $B$20:$B$44 and $B$56:$B$80 named as F_Shell. Formula =COUNTIF(F_Shell,"0"). This returns #VALUE, but it returns the correct number if I use the cell references. I've tried entering it as a CSE: no change. I've tried the suggestion from other questions in this forum to ensure it is not text format, but general + double-click + enter: no change. I'd appreciate any pointers. |
Countif + Named range
Are you saying that you can use COUNTIF with a non-contiguous range if you
use the actual cell references. If so, I would like to see your formula because I cannot get that to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jonathan" wrote in message ... I'm using a simple Countif in XL2003 so don't need to CSE it. It fights with a named range and I cannot see why. It worked when I set it up, but the moment I changed anything it stopped. Where am I going wrong? Given a 50-cell range $B$20:$B$44 and $B$56:$B$80 named as F_Shell. Formula =COUNTIF(F_Shell,"0"). This returns #VALUE, but it returns the correct number if I use the cell references. I've tried entering it as a CSE: no change. I've tried the suggestion from other questions in this forum to ensure it is not text format, but general + double-click + enter: no change. I'd appreciate any pointers. |
Countif + Named range
You were right to doubt me--I had used just a simplified contiguous version
of the range in my test. It was not until I used the full non-contiguous version that the range-naming died on me. Since COUNTIF is a specialised array/CSE function, does this mean that such a function won't work if the array is not contiguous(whatever the range might be named) i.e. it is not an array? "Bob Phillips" wrote: Are you saying that you can use COUNTIF with a non-contiguous range if you use the actual cell references. If so, I would like to see your formula because I cannot get that to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jonathan" wrote in message ... I'm using a simple Countif in XL2003 so don't need to CSE it. It fights with a named range and I cannot see why. It worked when I set it up, but the moment I changed anything it stopped. Where am I going wrong? Given a 50-cell range $B$20:$B$44 and $B$56:$B$80 named as F_Shell. Formula =COUNTIF(F_Shell,"0"). This returns #VALUE, but it returns the correct number if I use the cell references. I've tried entering it as a CSE: no change. I've tried the suggestion from other questions in this forum to ensure it is not text format, but general + double-click + enter: no change. I'd appreciate any pointers. |
Countif + Named range
COUNTIF is a function that processes a range of values, but it is not CSE,
and it cannot handle non-contiguous ranges, named or not. You need a different approach. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jonathan" wrote in message ... You were right to doubt me--I had used just a simplified contiguous version of the range in my test. It was not until I used the full non-contiguous version that the range-naming died on me. Since COUNTIF is a specialised array/CSE function, does this mean that such a function won't work if the array is not contiguous(whatever the range might be named) i.e. it is not an array? "Bob Phillips" wrote: Are you saying that you can use COUNTIF with a non-contiguous range if you use the actual cell references. If so, I would like to see your formula because I cannot get that to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jonathan" wrote in message ... I'm using a simple Countif in XL2003 so don't need to CSE it. It fights with a named range and I cannot see why. It worked when I set it up, but the moment I changed anything it stopped. Where am I going wrong? Given a 50-cell range $B$20:$B$44 and $B$56:$B$80 named as F_Shell. Formula =COUNTIF(F_Shell,"0"). This returns #VALUE, but it returns the correct number if I use the cell references. I've tried entering it as a CSE: no change. I've tried the suggestion from other questions in this forum to ensure it is not text format, but general + double-click + enter: no change. I'd appreciate any pointers. |
Countif + Named range
Thanks for this help Bob, and for your very fast responses. I will go and
de-discontiguise my tables! "Bob Phillips" wrote: COUNTIF is a function that processes a range of values, but it is not CSE, and it cannot handle non-contiguous ranges, named or not. You need a different approach. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jonathan" wrote in message ... You were right to doubt me--I had used just a simplified contiguous version of the range in my test. It was not until I used the full non-contiguous version that the range-naming died on me. Since COUNTIF is a specialised array/CSE function, does this mean that such a function won't work if the array is not contiguous(whatever the range might be named) i.e. it is not an array? "Bob Phillips" wrote: Are you saying that you can use COUNTIF with a non-contiguous range if you use the actual cell references. If so, I would like to see your formula because I cannot get that to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jonathan" wrote in message ... I'm using a simple Countif in XL2003 so don't need to CSE it. It fights with a named range and I cannot see why. It worked when I set it up, but the moment I changed anything it stopped. Where am I going wrong? Given a 50-cell range $B$20:$B$44 and $B$56:$B$80 named as F_Shell. Formula =COUNTIF(F_Shell,"0"). This returns #VALUE, but it returns the correct number if I use the cell references. I've tried entering it as a CSE: no change. I've tried the suggestion from other questions in this forum to ensure it is not text format, but general + double-click + enter: no change. I'd appreciate any pointers. |
Countif + Named range
You can do it with non-contiguous ranges, but not the way you tried. For
instance =SUMPRODUCT(COUNTIF(INDIRECT({"B20:B44","B56:B80"} ),"0")) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jonathan" wrote in message ... Thanks for this help Bob, and for your very fast responses. I will go and de-discontiguise my tables! "Bob Phillips" wrote: COUNTIF is a function that processes a range of values, but it is not CSE, and it cannot handle non-contiguous ranges, named or not. You need a different approach. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jonathan" wrote in message ... You were right to doubt me--I had used just a simplified contiguous version of the range in my test. It was not until I used the full non-contiguous version that the range-naming died on me. Since COUNTIF is a specialised array/CSE function, does this mean that such a function won't work if the array is not contiguous(whatever the range might be named) i.e. it is not an array? "Bob Phillips" wrote: Are you saying that you can use COUNTIF with a non-contiguous range if you use the actual cell references. If so, I would like to see your formula because I cannot get that to work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jonathan" wrote in message ... I'm using a simple Countif in XL2003 so don't need to CSE it. It fights with a named range and I cannot see why. It worked when I set it up, but the moment I changed anything it stopped. Where am I going wrong? Given a 50-cell range $B$20:$B$44 and $B$56:$B$80 named as F_Shell. Formula =COUNTIF(F_Shell,"0"). This returns #VALUE, but it returns the correct number if I use the cell references. I've tried entering it as a CSE: no change. I've tried the suggestion from other questions in this forum to ensure it is not text format, but general + double-click + enter: no change. I'd appreciate any pointers. |
All times are GMT +1. The time now is 01:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com