I'm trying to count some data in the worksheet to make a statistic, but it's not working:
Input data, in cells from B1 to B6
4.75x
5.76x
1.66x
11.17x
20.76x
Result I want to get
2.99x and < > 3.99x = 0
Greater than > 4 = 4
Note: I'm using the formulas in A8, A9, A10 e A11
=COUNTIF(A1:A5;"<1.99x")
=COUNTIF(A1:A5;"<2.99x")-A8
=COUNTIF(S1:A1:A5;">2.99x")-A11
=COUNTIF(A1:A5;">4.99x")
Anyone who can help will thank me.
To get simpler, first let's take the "x" number, so I did the conversion of the values to the column B , home line, B1 to B5 using the formula:
=LEFT($A1;LEN($A1)-1)*1
Then we have 4.75x has 5 characters, less 1 , gets 4.75 . So by removing the X , and the *1 to convert to Number. And we also have to convert from numbers with . to numbers with , . I suggest using Google's Find and Replace Ctrl + H .
Instead of using COUNTIF we will use COUNTIFS , which returns the count of a range, depending on several criteria.
So, according to your data the lines from B8 to B11, are with the formulas:
=COUNTIF(B1:B5;"<=1,99")
=COUNTIFS(B1:B5;">1,99";B1:B5;"<2,99")
=COUNTIFS(B1:B5;">2,99";B1:B5;"<3,99")
=COUNTIF(B1:B5;">4")
Roles used:
LEN = Returns the length of a string.
LEFT = Returns a substring from the beginning of a string specified.
COUNTIFS = Returns the count of a range, depending on various criteria.
Here's an example: How to Count Google Sheets