Shoutbox

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