# Count values between two criteria

1. ## 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. 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. 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. ## Count values between two criteria

Try one of these:

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

5. 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. 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. 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. 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...

9. 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. There are ways around that error... but the formula solution you've got works so may as well go with it.

11. ## Count values between two criteria

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. 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. ## 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,

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

14. 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,

``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. You have crossposted this question here: http://www.theofficeexperts.com/foru...ead.php?t=9515

16. Originally Posted by NBVC
You have crossposted this question here: http://www.theofficeexperts.com/foru...ead.php?t=9515

In fact I stopped the 3d in the other forum...anyway I didn`t know this rule...it will never happen again Sorry

17. 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. ## Count values between two criteria

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. 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

20. ## 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,

That should resolve the issue.

21. 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,

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

22. ## 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. 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

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

#### 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