Viewing 34 posts - 1 through 34 (of 34 total)
  • Excel help please
  • ash
    Full Member

    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!

    joemarshall
    Free Member

    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.

    mywebsites
    Free Member

    COUNTIF Syntax

    =countif(range,criteria)

    =COUNTIF(A1:A20,”>20″)

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    mywebsites
    Free Member

    Must have typed that reply at exactly the same time as joe.

    Stoner
    Free Member

    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.

    ash
    Full Member

    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 !

    Stoner
    Free Member

    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.

    acjim
    Free Member

    German excel – you lucky person 😉

    ash
    Full Member

    tell me about it

    ash
    Full Member

    thanks stoner, still can’t get it…. must have the translation of COUNTIF wrong 🙁

    Stoner
    Free Member
    ash
    Full Member

    nice one, but why isn’t this working then……?

    =ZÄHLENWENN(G3:G892,”<“&G893)

    (boundary value is in G893)

    Stoner
    Free Member

    if you replace “<“&G893 with “x” does it work?

    ash
    Full Member

    nope 🙁

    Stoner
    Free Member

    when you start typing ZÄHLENW… use the dialogue box to control the syntax

    Stoner
    Free Member

    oops my typo

    if you replace “<“&G893 with “<20” does it work?

    acjim
    Free Member

    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:

    {=SUM(IF(G3:G892<G893,1,0))}

    Use shift+cntl+return to set Arrays.

    ash
    Full Member

    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 ****) 🙁

    Stoner
    Free Member

    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?

    acjim
    Free Member

    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.

    ash
    Full Member

    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

    Stoner
    Free Member

    acjim – the problem is that its German Excel, not the formula.

    Just need to check that the function spellings and syntax punctuation is correct

    Stoner
    Free Member

    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

    in the first box you should put: G3:G892
    in the second box you should put: “<“&G893

    nickc
    Full Member

    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…

    nickc
    Full Member

    ignore me, I am talking carp

    acjim
    Free Member

    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?

    Stoner
    Free Member

    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.

    joemarshall
    Free Member

    This might be an obvious one, but you don’t have decimal places in the numbers with English . in them instead of German ,

    Joe

    stumpy01
    Full Member

    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.

    Stoner
    Free Member

    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.

    stumpy01
    Full Member

    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.

    acjim
    Free Member

    This must be the most “popular” excel help thread ever 😀

    Stoner
    Free Member

    because vee vont be beaten by zee germans!

    🙂

    scaredypants
    Full Member

    have you switched it off & back on again ?

Viewing 34 posts - 1 through 34 (of 34 total)

The topic ‘Excel help please’ is closed to new replies.