Excel help please

Home Forum Chat Forum Excel help please

Viewing 34 posts - 1 through 34 (of 34 total)
  • Excel help please
  • Premier Icon ash
    Subscriber

    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.

    mywebsites
    Member

    COUNTIF Syntax

    =countif(range,criteria)

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

    mywebsites
    Member

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

    Premier Icon Stoner
    Subscriber

    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.

    Premier Icon ash
    Subscriber

    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 !

    Premier Icon Stoner
    Subscriber

    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
    Member

    German excel – you lucky person 😉

    Premier Icon ash
    Subscriber

    tell me about it

    Premier Icon ash
    Subscriber

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

    Premier Icon ash
    Subscriber

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

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

    (boundary value is in G893)

    Premier Icon Stoner
    Subscriber

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

    Premier Icon ash
    Subscriber
    Premier Icon Stoner
    Subscriber

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

    Premier Icon Stoner
    Subscriber

    oops my typo

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

    acjim
    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.

    Premier Icon ash
    Subscriber

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

    Premier Icon Stoner
    Subscriber

    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
    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.

    Premier Icon ash
    Subscriber

    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

    Premier Icon Stoner
    Subscriber

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

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

    Premier Icon Stoner
    Subscriber

    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

    Premier Icon nickc
    Subscriber

    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…

    Premier Icon nickc
    Subscriber

    ignore me, I am talking carp

    acjim
    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?

    Premier Icon Stoner
    Subscriber

    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

    stumpy01
    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.

    Premier Icon Stoner
    Subscriber

    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
    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
    Member

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

    Premier Icon Stoner
    Subscriber

    because vee vont be beaten by zee germans!

    🙂

    Premier Icon scaredypants
    Subscriber

    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.