[Solved] Formula "Countif" with multiple worksheets

Discuss the spreadsheet application
Post Reply
Flsuperglide
Posts: 6
Joined: Sun Nov 05, 2017 1:28 am

[Solved] Formula "Countif" with multiple worksheets

Post by Flsuperglide »

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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Formula "Countif" with multiple worksheets.

Post by FJCC »

Try

Code: Select all

countif('week 1'.A1~'week 17'.A1;"=>10")
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Formula "Countif" with multiple worksheets.

Post by RusselB »

@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.
Flsuperglide
Posts: 6
Joined: Sun Nov 05, 2017 1:28 am

Re: Formula "Countif" with multiple worksheets.

Post by Flsuperglide »

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
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Formula "Countif" with multiple worksheets.

Post by robleyd »

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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Formula "Countif" with multiple worksheets.

Post by FJCC »

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?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Flsuperglide
Posts: 6
Joined: Sun Nov 05, 2017 1:28 am

Re: Formula "Countif" with multiple worksheets.

Post by Flsuperglide »

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.

Post by Flsuperglide »

FJCC, cell A1 contains a formula that adds up the players weekly wins.
OpenOffice 4.1.3. on Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Formula "Countif" with multiple worksheets.

Post by FJCC »

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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Flsuperglide
Posts: 6
Joined: Sun Nov 05, 2017 1:28 am

Re: Formula "Countif" with multiple worksheets.

Post by Flsuperglide »

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
(44.82 KiB) Downloaded 173 times
OpenOffice 4.1.3. on Windows 10
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Formula "Countif" with multiple worksheets.

Post by robleyd »

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

=COUNTIF(Sheet1.A71~Sheet2.A71;">=4")
or more simply you could do this:

Code: Select all

=COUNTIF(Sheet1.A71~Sheet2.A71;">3")
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Flsuperglide
Posts: 6
Joined: Sun Nov 05, 2017 1:28 am

Re: Formula "Countif" with multiple worksheets.

Post by Flsuperglide »

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
Post Reply