Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
possibly a bit of a numpty question:
Let's say I have a very long column of numbers; random numbers between 1 and 100
And let's say I want to highlight the numbers which are under, say, 50.... no problem, use conditional formatting or whatever it's called in the English version of Excel (I have the German version here at work).
But how about if I want to automatically count up how many of the numbers in this column have fulfilled the condition, i.e. without counting them out manually.
Ideally I could do with a figure at the bottom of the column of numbers telling me how many of them have a value less than "x" (won't always be 50!)
Any ideas ? Any input would be greatly appreciated!
you want to use =countif. I can't remember exactly how to use it, but it's pretty straightforward in the help for countif. I think it's
=countif(A2:A50,"<50") or something.
COUNTIF Syntax
=countif(range,criteria)
=COUNTIF(A1:A20,">20")
Must have typed that reply at exactly the same time as joe.
for variable criteria you have to use a modification to the syntax which Ive never seen documented anywhere very well:
=COUNTIF(A1:A20,">"&B1)
where B1 is your variable criteria.
Cheers but I can't get that to work
Stoner.... do I literally replace your B1 with the boundary value I want, e.g. 50, and it's only the "less than"/"more than" sign in inverted commas ?
Also, I can't work out what COUNTIF is in the German version of Excel, grrrr
could be ANZAHL, could be ANZAHL2 ... either way it isn't playing
Thanks for any more input !
ash - no B1 is an example of a cell reference where you can type the boundary value.
you put the operator <, > or =<, >= in quotations.
theres also a way of having the operator chosen in the sheet rathe rthan the forumla if you want.
German excel - you lucky person 😉
tell me about it
thanks stoner, still can't get it.... must have the translation of COUNTIF wrong 🙁
nice one, but why isn't this working then......?
=ZÄHLENWENN(G3:G892,"<"&G893)
(boundary value is in G893)
if you replace "<"&G893 with "x" does it work?
nope 🙁
when you start typing ZÄHLENW... use the dialogue box to control the syntax
oops my typo
if you replace "<"&G893 with "<20" does it work?
are you sure your test list G3:G892 is the same type as the condition? ie both the list and the condition contain only numbers.
The other way to do this is with an array formula:
[code]{=SUM(IF(G3:G892<G893,1,0))}[/code]
Use shift+cntl+return to set Arrays.
bleh, thanks guys but none of that is working, including acjim's array formula
and yes the test list / condition are nothing but numbers
bit stumped (and ****ed) 🙁
theres no reason that any of the above shouldnt work.
What does the syntax dialogue box look like when you type =ZÄHLENW and press the fx button next to the forumla bar?
What result do you get? Is it an error code?
You could try using DCOUNT but it's a bit clunky for a simple count.
stoner - sorry if this is a stupid question, but what do you mean by the "syntax dialogue box"
yeah acjim I just get an error code
annoying me this, as I've used excel a fair bit and haven't ever really got into too much trouble
acjim - the problem is that its German Excel, not the formula.
Just need to check that the function spellings and syntax punctuation is correct
when you click fx after typing the function name in the forumla box, then a dialogue box pops up with spaces to put the function arguments in.
for countif it should be
[img] 
[/img]
in the first box you should put: G3:G892
in the second box you should put: "<"&G893
To further muddy the water, shouldn't this be COUNTIFS rather than COUNTIF? the 'IFS' selects a criteria, then a range to apply the criteria?
Could be talking carp though...
ignore me, I am talking carp
If the array formula didn't work, assuming you used SUMME instead of SUM, then it's something to do with the workheet or the values. Try copying the values to a fresh window?
good point acjim - maybe there's "text" numbers in there as well as numerical numbers.
best cleaning technique is to multiply then divide by the smae number.
This might be an obvious one, but you don't have decimal places in the numbers with English . in them instead of German ,
Joe
I just did this: =COUNTIF(A1:A56,">50") in excel and it worked fine. Column A was a load of random numbers I put in. You don't need &B1 stuff - well not in Excel 2003 anyway!
I you want to make sure all your numbers are numbers.....just do format>cells>number tab and select 'number' to however many dp's you want.
stumpy - the reason for putting in the external criteria reference (e.g. B1) is so that you can vary the criteria when you want to without having to change the code.
and format cells number wont always work, its a common bug. The only surefire way of getting excel to treat it like a number if theres a format problem like that is to use it in a calculation to "clean" it.
Yep, I know why the cell reference was there, but seeing as the guy is having a bit of trouble, I thought it best to start off easy. Then when that works, it's possible to tinker with it from there but at least you have a working 'start' point.
I wasn't aware of the 'common bug' you mention, but most of the Excel stuff I do is with data generated by visioning systems and that always comes out as either numbers or 'na' and we filter for 'na' before doing anything with it.
This must be the most "popular" excel help thread ever 😀
because vee vont be beaten by zee germans!
🙂
have you switched it off & back on again ?