Please help to write : Countif (range: <0.4 and >-3)
I need to count the number of cells within a range (e.g. A10:Z10)
each cell with the criteria of <0.4 and >-3
Please help to make it.
Eduardo
Please help to write : Countif (range: <0.4 and >-3)
I need to count the number of cells within a range (e.g. A10:Z10)
each cell with the criteria of <0.4 and >-3
Please help to make it.
Eduardo
Last edited by EduardoDon; 05-19-2005 at 11:09 AM.
A couple of approaches come readily to mind.
My personal choice would be:
A11=AND(a10>-3,a1<0.4) copied across to Z11 to yield a row of TRUE/FALSE's and then apply your COUNTIF to that row =COUNTIF(a11:z11,TRUE)
Alternatively (and someone will correct me if I get the syntax wrong), use an array SUMPRODUCT formula
=SUMPRODUCT(--(a10:z10>-3),--(a10:z10<0.4))
confirmed with ctrl-shift-enter (to make it an array formula).
Dear Mr. Shorty,
Your first choice will not be applicable 'cos the cells already have formulae.
When I use your second choice, it return in "#value" ?
What have I done wrong ?
Eduardo
The second formula from Mr. Shorty is correct
=SUMPRODUCT(--(a10:z10>-3),--(a10:z10<0.4))
This should work with or without being Array entered (CTRL+SHIFT+ENTER)
Double check your entries to make sure they are valid.
Another option is to use this formula:
=COUNTIF(A10:Z10,"<0.4")-COUNTIF(A10:Z10,"<-3")
or this:
=COUNTIF(A10:H10,">-3")-COUNTIF(A10:H10,">0.4")
Good Luck
Last edited by swatsp0p; 05-20-2005 at 10:41 AM.
Bruce
The older I get, the better I used to be.
USA
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks