Shoutbox

Try doing a search for excel formulas or something along that line.

Cheers Saralk the formulae actually gives a result but not the right result. lolololol. hmmmm isnt it possible to do =COUNTIF(A1:H1, "8to10") sort of thing.

quote:

---

Originally posted by may73alliance
Woah thanks cookie. ive tryed all of them an i get an error saying "the formula you typed contains an error"

---

Make 100% sure that you typed the formula correctly

Also note that the formula given is in English... Excel is a language dependant application, this means that the formula's are different in different languages.

eg: the English Excel formula SUM() would be SOM() in Dutch Excel, etc...

quote:

---

Originally posted by may73alliance
What i am trying to do is count how many blocks have a number between 8 and 10 on them in a specific range.

---

Like first said by Z_Joanna_ZZZ and corrected by me:
* get number of cells in the range A1:A55 which have a value between 8 and 10 (thus excl. 8 and 10):
=COUNTIF(A1:A55;">8")-COUNTIF(A1:A55;">=10")

* get number of cells in the range A1:A55 which have a value from 8 to 10 (thus incl. 8 and 10):
=COUNTIF(A1:A55;">=8")-COUNTIF(A1:A55;">10")

quote:

---

Originally posted by serpico
try http://support.microsoft.com they have a great database for excel

---

Excel comes with a very extensive helpfile already, pressing F1 is all it takes

quote:

---

Originally posted by saralk
ok, i just made a simple formula now, it works for me, but its not inclusive
=COUNTIF(A1:H1, ">8")-COUNTIF(A1:H1, "<10")

---

That's wrong and wont give the correct result, it should be ">", not "<". And if you want to make it between 8 and 10 (thus not including 8 and 10), it should even be ">=10"

Besides, the (almost correct) formula was already given in the second post of this thread (and again in my first post)

quote:

---

Originally posted by may73alliance
Cheers Saralk the formulae actually gives a result but not the right result. lolololol. hmmmm isnt it possible to do =COUNTIF(A1:H1, "8to10") sort of thing.

---

read my first post in this thread?

quote:

---

Originally posted by CookieRevised

quote:

---

Originally posted by may73alliance
Cheers Saralk the formulae actually gives a result but not the right result. lolololol. hmmmm isnt it possible to do =COUNTIF(A1:H1, "8to10") sort of thing.

---

read my first post in this thread?

---

lol, yea, sorry i got confused with all the info! lol

I typed in the one you said you corrected and got that damn error again. i ajusted saralk's one a bit to =COUNTIF(J1:J98, ">=8")-COUNTIF(J1:J98, ">=10") and that works i think thats right anyway

No, it will NOT return the correct result...

Please read my posts carefully, and notice the colored, bold or underlined parts!!!

quote:

---

Originally posted by CookieRevised
Like first said by Z_Joanna_ZZZ and corrected by me:
* number of cells in range A1:A55 which have a value between 8 and 10 (thus excl. 8 and 10):
=COUNTIF(A1:A55;">8")-COUNTIF(A1:A55;">=10")

* number of cells in range A1:A55 which have a value from 8 till 10 (thus incl. 8 and 10):
=COUNTIF(A1:A55;">=8")-COUNTIF(A1:A55;">10")

---

notice the red and blue parts



if the range is:
  5.77
  6.45
  8.00
  8.16
  9.51
  10.00
  10.78

the first formula:
=COUNTIF(A1:A55;">8") - COUNTIF(A1:A55;">=10")
=4 - 2
=2
(because only 8.16 and 9.51 are numbers between 8 and 10)

the second formula:
=COUNTIF(A1:A55;">=8") - COUNTIF(A1:A55;">10")
=5 - 1
=4
(because 8.00, 8.16, 9.51 and 10.00 are numbers from 8 till 10, inclusief 10)

your formula:
=COUNTIF(A1:A55;">=8")-COUNTIF(A1:A55;">=10")
=5 - 2
=3 (thus wrong)
=the amount of numbers from 8 (thus incl. 8) to 10 (thus excl. 10)

">" means bigger then. This means that ">8" means bigger then 8... 8 is not bigger then 8 but equal... Thus 8 will not be counted...

">=" means bigger then or equal to. This means that ">=8" means bigger then 8 or equal to 8... Thus 8 will be counted...

You can use the insert funtion button and use the wizard like screen to enter the range and criteria etc. so that you don't get confused with how to enter it.


Click for better view

woah cheers ppl.  thanks a lot for all of the help. still think MS should just make it so you can type between. lol