[Solved] Formula "Countif" with multiple worksheets

[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.

[FJCC]

Try

Code: Select all   Expand viewCollapse view

countif('week 1'.A1~'week 17'.A1;"=>10")

[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.

[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.

[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.

[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?

[Flsuperglide]

FJCC, The number is in green

[Flsuperglide]

FJCC, cell A1 contains a formula that adds up the players weekly wins.

[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.

[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

[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   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")

[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