Results 1 to 9 of 9
Thread: Countif syntax (Excel 2000 SP3)

20060215, 10:21 #1
 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 multisheet 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

20060215, 10:35 #2
 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 )

20060215, 11:36 #3
 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

20060215, 12:07 #4
 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.

20060215, 12:57 #5
 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

20060215, 13:12 #6
 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 1165'!$B$7:$B$838,'Bolt nos'!C15)+COUNTIF('Up 166240'!$B$7:$B$310,'Bolt nos'!C15)+COUNTIF('Up 241291'!$B$7:$B$222,'Bolt nos'!C15)+COUNTIF('Down 147'!$B$7:$B$206,'Bolt nos'!C15)+COUNTIF('Down 4887'!$B$7:$B$166,'Bolt nos'!C15)+COUNTIF('Down 88198'!$B$7:$B$718,'Bolt nos'!C15+COUNTIF('Down 199299'!$B$7:$B$514,'Bolt nos'!C15)+COUNTIF('Down 300419'!$B$7:$B$600,'Bolt nos'!C15)+COUNTIF('Down 420a697'!$B$7:$B$1474,'Bolt nos'!C15))
there is no closing parenthesis in COUNTIF('Down 88198'!$B$7:$B$718,'Bolt nos'!C15+, and hence an extra closing parenthesis at the end. The formula should be
=COUNTIF('Up 1165'!$B$7:$B$838,'Bolt nos'!C15)+COUNTIF('Up 166240'!$B$7:$B$310,'Bolt nos'!C15)+COUNTIF('Up 241291'!$B$7:$B$222,'Bolt nos'!C15)+COUNTIF('Down 147'!$B$7:$B$206,'Bolt nos'!C15)+COUNTIF('Down 4887'!$B$7:$B$166,'Bolt nos'!C15)+COUNTIF('Down 88198'!$B$7:$B$718,'Bolt nos'!C15)+COUNTIF('Down 199299'!$B$7:$B$514,'Bolt nos'!C15)+COUNTIF('Down 300419'!$B$7:$B$600,'Bolt nos'!C15)+COUNTIF('Down 420a697'!$B$7:$B$1474,'Bolt nos'!C15)

20060215, 13:43 #7
 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 preflag this so that the range which appears in the formula has $ signs (absolute?)?
Just wondered....
Neil

20060215, 13:46 #8
 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 4way 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.

20060215, 13:54 #9
 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