+ Reply to Thread
Results 1 to 23 of 23

Count values between two criteria

  1. #1
    Registered User
    Join Date
    05-27-2008
    Location
    Milano, Italy
    Posts
    21

    Count values between two criteria

    Hi everybody
    I got a problem, I can`t count the number of values between two criteria..
    For example I got in a column a data series like this
    Coloumn "H"

    1
    2
    Not disclosed
    2
    Not disclosed
    100

    I want to count the number of cells between 1 and 10 ( The result is 3 in this case)...i tried a lot of forulas but i didn`t succed in....
    thanks for help

  2. #2
    Valued Forum Contributor Shijesh Kumar's Avatar
    Join Date
    05-26-2008
    Location
    Bangalore / India
    MS-Off Ver
    2000
    Posts
    717
    Hi,

    Suppose your data is in cell A1 to A100 then to count the number of cell with any criteria like nos. between 1 and 10 you can use Array Formuals.

    Eg.

    ={sum((A1:A100>=1) * ( A1:A100)<=10) )}

    Enter the above formula without curly braces ... then press ctrl+shift+enter
    curly brace will automatically appear....

  3. #3
    Registered User
    Join Date
    05-27-2008
    Location
    Milano, Italy
    Posts
    21
    Quote Originally Posted by Shijesh Kumar
    Hi,

    Suppose your data is in cell A1 to A100 then to count the number of cell with any criteria like nos. between 1 and 10 you can use Array Formuals.

    Eg.

    ={sum((A1:A100>=1) * ( A1:A100)<=10) )}

    Enter the above formula without curly braces ... then press ctrl+shift+enter
    curly brace will automatically appear....
    I`m trying but it doesn`t work....

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Count values between two criteria

    Try one of these:

    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  5. #5
    Registered User
    Join Date
    05-27-2008
    Location
    Milano, Italy
    Posts
    21
    The formulas are correct but i can't understant why they don`t work...the data are coming from a SUBSTITUTE formula (original data G23m, data of coloumn H 23) but even if I paste the values the result is always 0....I use Excel 2000, unfortunately I can`t post a copy of the document...

  6. #6
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    I suspect that the Col_H values are text, not numeric.

    Try this formula:

    Please Login or Register  to view this content.
    Does that resolve the problem?

  7. #7
    Registered User
    Join Date
    05-27-2008
    Location
    Milano, Italy
    Posts
    21
    That was the problem and with the formula everything work...anyway is there a way to change the values from text to number or Have I to maintain it in text value....Thanks again

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    One way to convert to numbers is to add a +0 to the end of your Substitute() formula in column H.

    This will convert numbers extracted as text to General numbers...
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  9. #9
    Registered User
    Join Date
    05-27-2008
    Location
    Milano, Italy
    Posts
    21
    Yes it`s a possible way but when i got the value "Not disclosed" excel give me the #VALUE error...so i think that I`ll extract numbers value from text with this formula

    =SUMPRODUCT((TEXT(H1:H1000,"00000")>="00001")*(TEXT(H1:H1000,"00000")<="00010"))+0

    In the way that i could create graphs from the values found (that are numeric values now)...

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    There are ways around that error... but the formula solution you've got works so may as well go with it.

  11. #11
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Count values between two criteria

    Quote Originally Posted by The_jackal
    ...anyway is there a way to change the values from text to number or Have I to maintain it in text value....Thanks again
    If you post your SUBSTITUTE formula, we may be able to come up with a formula you can use.

  12. #12
    Registered User
    Join Date
    05-27-2008
    Location
    Milano, Italy
    Posts
    21
    Now that I have to sum the values I obtained there is another problem...I can`t sum the text value...and if I trasform them in numbers value I got error #VALUE from the cell where there is "Not disclosed"
    This is the SUBSTITUTE formula I use

    =SUBSTITUTE(SUBSTITUTE(D3,"G",""),"m","")

  13. #13
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Count values between two criteria

    If you need to sum the "values" between 1 and 10, inclusive,
    try something like this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER,
    instead of ENTER:

    Please Login or Register  to view this content.
    Is that something you can work with?

  14. #14
    Registered User
    Join Date
    05-27-2008
    Location
    Milano, Italy
    Posts
    21
    Quote Originally Posted by Ron Coderre
    If you need to sum the "values" between 1 and 10, inclusive,
    try something like this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER,
    instead of ENTER:

    Please Login or Register  to view this content.
    Is that something you can work with?
    Sorry but i think i`ve explained the situation in a bad way...because of my english...
    For example in the coloumn H i got different values
    1
    2
    3
    not disclosed
    5
    2
    5
    not disclosed

    3

    These are all text values because i used this formula to obtain them
    =SUBSTITUTE(SUBSTITUTE(D3,"G",""),"m","")

    Now I`m able to count the numbers in a certain range with the formula
    =SUMPRODUCT((TEXT(H1:H1000,"00000")>="00001")*(TEXT(H1:H1000,"00000")<="00010"))

    I also need to sum all the numeric values...(result of the example is 21) but I`m not able because i haven`t got numeric values but text values
    If i try to change the values in a numeric value I obtain the #VALUE error in corrispondence of the "not disclosed" array...

  15. #15
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    You have crossposted this question here: http://www.theofficeexperts.com/foru...ead.php?t=9515

    Please read our forum rules about crossposting and consuming several people's time and effort.

  16. #16
    Registered User
    Join Date
    05-27-2008
    Location
    Milano, Italy
    Posts
    21
    Quote Originally Posted by NBVC
    You have crossposted this question here: http://www.theofficeexperts.com/foru...ead.php?t=9515

    Please read our forum rules about crossposting and consuming several people's time and effort.
    In fact I stopped the 3d in the other forum...anyway I didn`t know this rule...it will never happen again Sorry

  17. #17
    Registered User
    Join Date
    05-27-2008
    Location
    Milano, Italy
    Posts
    21
    just a little update
    If i try to obtain all numeric values with the formula

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"G",""),"m",""),"Not disclosed","")+0

    I got no problem with cell (for example g23m) but in correspondence to the cells with "Not disclosed" I got the #VALUE...is there a way to avoid it?

    Thanks

  18. #18
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Count values between two criteria

    Quote Originally Posted by The_jackal
    I also need to sum all the numeric values...(result of the example is 21) but I`m not able because i haven`t got numeric values but text values
    If i try to change the values in a numeric value I obtain the #VALUE error in corrispondence of the "not disclosed" array...
    Perhaps something like this will work for you:
    This ARRAY FORMULA sums all numeric values in the referenced range
    =SUM(IF(ISNUMBER(--H1:H1000),H1:H1000))

    Note: the double-minus is used to effect a conversion from text to numbers.
    You could also add (or subtract) zero or divide or multiply by one, but
    the double-minus is a clear indication that a conversion is intended, and
    not an arithmetic operation.

    Does that help?

  19. #19
    Registered User
    Join Date
    05-27-2008
    Location
    Milano, Italy
    Posts
    21
    The formula give me the result of 0....
    I attach a sample of the document, the data of coloumn H are the text values....i hope this will be useful for you, thanks again
    Attached Files Attached Files

  20. #20
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Count values between two criteria

    The formula is an ARRAY FORMULA.

    As I mentioned a few posts ago:
    "ARRAY FORMULA, committed with CTRL+SHIFT+ENTER,
    instead of ENTER"

    That should resolve the issue.

  21. #21
    Registered User
    Join Date
    05-27-2008
    Location
    Milano, Italy
    Posts
    21
    Quote Originally Posted by Ron Coderre
    The formula is an ARRAY FORMULA.

    As I mentioned a few posts ago:
    "ARRAY FORMULA, committed with CTRL+SHIFT+ENTER,
    instead of ENTER"

    That should resolve the issue.
    It`s entered like an Array formula but the result is anyway 0....

  22. #22
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Count values between two criteria

    When I open the workbook you posted and
    put this ARRAY FORMULA in one of the cells:

    =SUM(IF(ISNUMBER(--H1:H10),--H1:H10))

    it returns: 14878

    I can't think of any other suggestions to offer.

  23. #23
    Registered User
    Join Date
    05-27-2008
    Location
    Milano, Italy
    Posts
    21
    Quote Originally Posted by Ron Coderre
    When I open the workbook you posted and
    put this ARRAY FORMULA in one of the cells:

    =SUM(IF(ISNUMBER(--H1:H10),--H1:H10))

    it returns: 14878

    I can't think of any other suggestions to offer.
    You`re right but if If I try to apply the formula to the other cell the formula doesn`t work anymore...so I changed the original formula to obtain all numeric values without errors in correspondence of "not disclosed"

    =(SUBSTITUTE((SUBSTITUTE(SUBSTITUTE(D35,"GBP",""),"m","")),"Not disclosed",0))+0

    Edit: your formula is also working, i think i`ll use it

    Thanks for help
    Last edited by The_jackal; 06-04-2008 at 11:37 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1