[Solved] Formula "Countif" with multiple worksheets
-
- Posts: 6
- Joined: Sun Nov 05, 2017 1:28 am
[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.
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
Re: Formula "Countif" with multiple worksheets.
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
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.
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.
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.
-
- Posts: 6
- Joined: Sun Nov 05, 2017 1:28 am
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
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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
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?
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
-
- Posts: 6
- Joined: Sun Nov 05, 2017 1:28 am
Re: Formula "Countif" with multiple worksheets.
FJCC, The number is in green
OpenOffice 4.1.3. on Windows 10
-
- 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
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.
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
-
- Posts: 6
- Joined: Sun Nov 05, 2017 1:28 am
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
Thanks again.sheet
- Attachments
-
- FOOTBALL OO HEJP FILE.ods
- sheet 3 yellow highlighted cells are my problem children
- (44.82 KiB) Downloaded 176 times
OpenOffice 4.1.3. on Windows 10
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.
or more simply you could do this:
Code: Select all
=COUNTIF(Sheet1.A71~Sheet2.A71;">=4")
Code: Select all
=COUNTIF(Sheet1.A71~Sheet2.A71;">3")
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
-
- Posts: 6
- Joined: Sun Nov 05, 2017 1:28 am
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
Thanks again,
Mark
OpenOffice 4.1.3. on Windows 10