Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Countif syntax (Excel 2000 SP3)

    Can someone help me with some syntax please?

    I have a multi-sheet model, and am looking to count the number of incidences of a particular date, in a particular column, on a number of sheets.

    In the simplest form, I am entering the formula on sheet 1, and will be looking to count the number of incidences of a particular date (say 19/11/05) in a particular range of rows in column B, on sheet 2, and another range of rows in columnt B on sheet 3 etc.

    Thanks

    Neil

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Countif syntax (Excel 2000 SP3)

    If the ranges of rows are different, you'll have to add the COUNTIFs for the individual sheets. Let's say you put the date you want to count (19/11/05) in cell D1 on Sheet1. You can use a formula like this:

    =COUNTIF(Sheet1!B3:B20,Sheet1!D1)+COUNTIF(Sheet2!B 12:B38,Sheet1!D1)+COUNTIF(Sheet3!B59:B83,Sheet1!D1 )

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Countif syntax (Excel 2000 SP3)

    Thanks Hans.

    That nearly worked!!

    My model is quite complex, and actually has 10 named worksheets. I've put together the formula, as you suggested and tried to test it.

    It only gave a total of 3 incidences of the date in question, but I knew there were more:-

    I've created a much shorter formula in the next cell right, and this one just takes one sheet in isolation, giving a total of 15 incidences.

    My original formula includes that sheet, somewhere in the middle, yet only gives a total of 3.

    Is there a limit to the number of characters in an individual formula? My original has c. 500 characters, and though it doesn't return an error, I wondered if the erroneous answer (3) is being returned because the formula is only looking at the first few sheets, and then running out of "capacity"?

    Or am I on completely the wrong track!!

    Neil

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Countif syntax (Excel 2000 SP3)

    A formula can be 1,024 characters in length, so 500 characters shouldn't be a problem.

    I don't see why an expression should work correctly by itself, but not as part of a larger expression. If you wish, you can post a stripped down and zipped copy of the workbook.

    As a workaround, you could create individual formulas for each of the worksheets (one COUNTIF per formula), then add the results of those in another formula.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Countif syntax (Excel 2000 SP3)

    Hans

    I will go with the workaround if necessary, but attached is the stripped down version of my file (with the headers and contents of irrelevant cells deleted)

    I wondered as doing this, is it the fact the dates are in a merged cell which is throwing out the formula results?

    Thanks for your trouble.

    Neil

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Countif syntax (Excel 2000 SP3)

    There is a misplaced parenthesis in your formula: in

    =COUNTIF('Up 1-165'!$B$7:$B$838,'Bolt nos'!C15)+COUNTIF('Up 166-240'!$B$7:$B$310,'Bolt nos'!C15)+COUNTIF('Up 241-291'!$B$7:$B$222,'Bolt nos'!C15)+COUNTIF('Down 1-47'!$B$7:$B$206,'Bolt nos'!C15)+COUNTIF('Down 48-87'!$B$7:$B$166,'Bolt nos'!C15)+COUNTIF('Down 88-198'!$B$7:$B$718,'Bolt nos'!C15+COUNTIF('Down 199-299'!$B$7:$B$514,'Bolt nos'!C15)+COUNTIF('Down 300-419'!$B$7:$B$600,'Bolt nos'!C15)+COUNTIF('Down 420a-697'!$B$7:$B$1474,'Bolt nos'!C15))

    there is no closing parenthesis in COUNTIF('Down 88-198'!$B$7:$B$718,'Bolt nos'!C15+, and hence an extra closing parenthesis at the end. The formula should be

    =COUNTIF('Up 1-165'!$B$7:$B$838,'Bolt nos'!C15)+COUNTIF('Up 166-240'!$B$7:$B$310,'Bolt nos'!C15)+COUNTIF('Up 241-291'!$B$7:$B$222,'Bolt nos'!C15)+COUNTIF('Down 1-47'!$B$7:$B$206,'Bolt nos'!C15)+COUNTIF('Down 48-87'!$B$7:$B$166,'Bolt nos'!C15)+COUNTIF('Down 88-198'!$B$7:$B$718,'Bolt nos'!C15)+COUNTIF('Down 199-299'!$B$7:$B$514,'Bolt nos'!C15)+COUNTIF('Down 300-419'!$B$7:$B$600,'Bolt nos'!C15)+COUNTIF('Down 420a-697'!$B$7:$B$1474,'Bolt nos'!C15)

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Countif syntax (Excel 2000 SP3)

    Doh!

    Thanks Hans. When I first created the formula and clicked enter, Excel said my formula contained an error, but it suggested an alternative, which I must admit I just accepted! I imagine it was just adding a closing parenthesis at the end, because I'd missed one out in the middle....

    One final question, if you don' t mind:-

    When I first generated the formula, I entered the ranges from the later sheets by clicking on those sheets and highlighting the range (obviously). But because I then wanted to be able to copy my formula down the table in sheet 1, but retain the ranges from the later sheets intact, I had to edit the formula manually to add $ signs to all the range references from the later sheets.

    Is there a way, when scrolling through a range of cells to create a range to go within a formula, to somehow pre-flag this so that the range which appears in the formula has $ signs (absolute?)?

    Just wondered....

    Neil

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Countif syntax (Excel 2000 SP3)

    Immediately after pointing at a range, press F4 to change the reference from relative to absolute. In fact, F4 acts as a 4-way toggle between all combinations of relative and absolute row and column references.

    You can also edit an existing formula, select a cell reference and press F4 to change absolute/relative.

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Posts
    155
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Countif syntax (Excel 2000 SP3)

    Thank you Hans

    Your assistance through these forums is really appreciated.

    Neil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •