## [Solved] Formula "Countif" with multiple worksheets

### [Solved] Formula "Countif" with multiple worksheets

Hello, this is my first post and I am really in need of help. I tried many hours searching for an answer without finding one so please forgive me if this has been covered before. I have windows 10 on an old Gateway laptop and version 4.3.1 of Oo. I run a little game that tracks how well players are able to pick winners of American Football (N.F.L.) games. On my last sheet, Year to Date, I would like a formula that would count the number of weeks that someone has picked 10 or more winners. l thought that "countif('week 1'.A1:'week 17'.A1;=>10)" would do it, but no. I get an error code 510 and I'm lost. I've been able to self teach myself everything else but this has me stumped. PLEASE HELP.
Thank you.
Last edited by Flsuperglide on Sun Nov 05, 2017 8:43 am, edited 1 time in total.
OpenOffice 4.1.3. on Windows 10
Flsuperglide

Posts: 6
Joined: Sun Nov 05, 2017 1:28 am

### Re: Formula "Countif" with multiple worksheets.

Try
Code: Select all   Expand viewCollapse view
`countif('week 1'.A1~'week 17'.A1;"=>10")`
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7870
Joined: Sat Nov 08, 2008 8:08 pm

### Re: Formula "Countif" with multiple worksheets.

@FJCC: In your suggested code you appear to have a ~ (tilde) rather than a : (colon).
I would appreciate an explanation, as I don't recall ever seeing the ~ character used in a Calc formula before.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6270
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Formula "Countif" with multiple worksheets.

countif('week 1'.A1~'week 17'.A1;"=>10") seems to want to work, no error message, but returns a value of 0, which I know to be incorrect. We are getting closer but still no cigar. If l just do "=countif('week1'.a1;"=6*)" and week1 cell a1 IS 6, l get the correct answer of 1. When l try to add additional sheets it stops working.
Last edited by Flsuperglide on Sun Nov 05, 2017 5:38 am, edited 1 time in total.
OpenOffice 4.1.3. on Windows 10
Flsuperglide

Posts: 6
Joined: Sun Nov 05, 2017 1:28 am

### Re: Formula "Countif" with multiple worksheets.

Search the offline help - F1- for Operator and scroll down to Reference operators; seems the tilde is only recently introduced as Concatenation or union operator.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 3639
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

### Re: Formula "Countif" with multiple worksheets.

The result of zero suggests that you have text instead of numbers in the cells. Select the menu View -> Value Highlighting. Numbers will then be blue, text black and formula results green. What color is week 1'.A1?
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7870
Joined: Sat Nov 08, 2008 8:08 pm

### Re: Formula "Countif" with multiple worksheets.

FJCC, The number is in green
OpenOffice 4.1.3. on Windows 10
Flsuperglide

Posts: 6
Joined: Sun Nov 05, 2017 1:28 am

### Re: Formula "Countif" with multiple worksheets.

FJCC, cell A1 contains a formula that adds up the players weekly wins.
OpenOffice 4.1.3. on Windows 10
Flsuperglide

Posts: 6
Joined: Sun Nov 05, 2017 1:28 am

### Re: Formula "Countif" with multiple worksheets.

Please post the actual formula or, better, a calc file showing the problem. To upload a file, click Post Reply then look for the Upload Attachment tab under the box where you type a response.
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7870
Joined: Sat Nov 08, 2008 8:08 pm

### Re: Formula "Countif" with multiple worksheets.

I highlighted the 2 cells in yellow on sheet 3 that are a good example of what I'm trying to do. Sorry for the delay, the original was way too big so I had to pluck 3 sheets out.
Thanks again.sheet
Attachments
FOOTBALL OO HEJP FILE.ods
sheet 3 yellow highlighted cells are my problem children
OpenOffice 4.1.3. on Windows 10
Flsuperglide

Posts: 6
Joined: Sun Nov 05, 2017 1:28 am

### Re: Formula "Countif" with multiple worksheets.

Calc doesn't like your syntax, apparently Reverse the order of your equal to or greater than operators and you get a non-zero result.

Code: Select all   Expand viewCollapse view
`=COUNTIF(Sheet1.A71~Sheet2.A71;">=4")`

or more simply you could do this:
Code: Select all   Expand viewCollapse view
`=COUNTIF(Sheet1.A71~Sheet2.A71;">3")`
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 3639
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

### Re: Formula "Countif" with multiple worksheets.

OMG, I am truly amazed and greatful of the quality people who donate their time and knowledge to help us folks that get stumped. I am in your debt for helping me with my issues. Hopefully I will be able to pay it forward in your honor.
Thanks again,
Mark
OpenOffice 4.1.3. on Windows 10
Flsuperglide

Posts: 6
Joined: Sun Nov 05, 2017 1:28 am