[Solved] Formula "Countif" with multiple worksheets

Discuss the spreadsheet application

[Solved] Formula "Countif" with multiple worksheets

Postby Flsuperglide » Sun Nov 05, 2017 1:55 am

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.

Postby FJCC » Sun Nov 05, 2017 4:36 am

Try
Code: Select all   Expand viewCollapse view
countif('week 1'.A1~'week 17'.A1;"=>10")
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7195
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Formula "Countif" with multiple worksheets.

Postby RusselB » Sun Nov 05, 2017 4:54 am

@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.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5183
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Formula "Countif" with multiple worksheets.

Postby Flsuperglide » Sun Nov 05, 2017 5:20 am

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.

Postby robleyd » Sun Nov 05, 2017 5:25 am

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 Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2810
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Formula "Countif" with multiple worksheets.

Postby FJCC » Sun Nov 05, 2017 5:34 am

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
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7195
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Formula "Countif" with multiple worksheets.

Postby Flsuperglide » Sun Nov 05, 2017 5:41 am

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.

Postby Flsuperglide » Sun Nov 05, 2017 5:45 am

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.

Postby FJCC » Sun Nov 05, 2017 6:11 am

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
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7195
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Formula "Countif" with multiple worksheets.

Postby Flsuperglide » Sun Nov 05, 2017 7:34 am

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 61 times
OpenOffice 4.1.3. on Windows 10
Flsuperglide
 
Posts: 6
Joined: Sun Nov 05, 2017 1:28 am

Re: Formula "Countif" with multiple worksheets.

Postby robleyd » Sun Nov 05, 2017 8:10 am

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 Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2810
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Formula "Countif" with multiple worksheets.

Postby Flsuperglide » Sun Nov 05, 2017 8:37 am

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


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests