Shoutbox

For The Functions formulae how do u specify something like =countif (a1:a55,BETWEEN 8 AND 10)

i cant seem to work it out and its really starting to bug me now

thanks.

i am not sure but..
=countif(A1:A55,">8")-countif(A1:A55,">10")

??? i'm not sure if this will work i havent used excel in a long long while..

i'm sure it will count the # of values between 8 and 10 in the range you specified though...

EDIT: let me know if it worked

hmm good idea,

it adds up the data in the ones that fit the specified countif criteria. hmmm this is confusing, why cant they just have it so you can say between8and10, lol

lol, i know, it pissed me off before too.

i think there is a shorter formula for this though, but i cant remember it

lol. ok, thansk anyway. hmmm could ask my ict teacher got that tomorrow, normally tell him the bloody answers is the only problem.

quote:

---

Originally posted by may73alliance
=countif (a1:a55,BETWEEN 8 AND 10)

---

What do you mean with that?



Do you mean:
- add every cell up from A1 till A55
- check if the total is between 8 and 10
=> =IF(AND(SUM(A1:A55)>8;SUM(A1:A55)<10);"in between";"not ok")

Or do you mean:
- add every cell up from A1 till A55 but only if the cell is between 8 and 10?
=> In this case you need a matrix formula. This is entered a bit different then useuall. Also, this is not commonly known/used!

The formula:
=SUM(IF((A1:A55>8)*(A1:A55<10);A1:A55))
But instead of pressing the useuall <ENTER> after you've typed it, you need to press <CTRL><SHIFT><ENTER>.

If you do this you'll see that Excel will add "{" and "}" around the whole formula. This makes it a matrix formula; the region(s) will depict every element of the matrix separately instead of taking the whole matrix/region. In the formula textbox you'll see:
{=SUM(IF((A1:A55>8)*(A1:A55<10);A1:A55))}

Note: Do NOT add the "{" and "}" manually, it will not work! You need to press <CTRL><SHIFT><ENTER>.

As soon as you edit the formula again, the "{" and "}" will automatically be removed, so you need to press <CTRL><SHIFT><ENTER> again after the edit.



Note: the above matrix formula can be achieved with the normal formula:
=SUMIF(A1:A55;">8")-SUMIF(A1:A55;">10")
(this method will not work in all cases. But for this case it does)


EDIT:
Oh... well... I didn't had a look at the thread again before posting (there wasn't any post yet when I started to write)

I forgot that COUNTIF is a valid English Excel formula and didn't linked it to the Dutch formula AANTAL, so I didn't knew what you meant ... So yes, what Z_Joanna_ZZZ told ya was almost correct:
=COUNTIF(A1:A55;">8")-COUNTIF(A1:A55;">=10")

Notes:
* there is no "shorter" normal formula for this. Unless you make it into a matrix formula like explained above:
{=COUNT(IF((A1:A55>8)*(A1:A55<10);A1:A55))}

* if you meant x should be bigger then 8 and smaller or equal then 10, you must do:
=COUNTIF(A1:A55;">8")-COUNTIF(A1:A55;">10")
or even:
=INDEX(INTERVAL(A1:A55;{8;10});2)
or as a matrix formula:
{=COUNT(IF((A1:A55>8)*(A1:A55<=10);A1:A55))}





PS: pressing F1 when you're cursor is on a formula can help a lot

Woah thanks cookie. ive tryed all of them an i get an error saying

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

thanks.

try http://support.microsoft.com they have a great database for excel

oooo, thanks. *on that now*

EDIT: ive got to http://support.microsoft.com/default.aspx/ph/2512?sid=202 but i cant find where it would be. Is there a like technical term for what i'm looking for that i can search for? thanks.

ok, i just made a simple formula now, it works for me, but its not inclusive

=COUNTIF(A1:H1, ">8")-COUNTIF(A1:H1, "<10")