Hello,
I have a document with sheets ranging from the years 2010 till 2017.
Every sheet contains a column D with a random number of cells containing color-names.
Now I’d like to count these cells with a specific color-name.
I tried the following:
=COUNTIF(‘2010’.D:D~‘2017’.D:D,“Yellow”)
but I get meaningless results.
My method combines two functions which work flawlessly on their own, but this is as far as I can get…
Could you say something more about your result than “meaningless”?
=COUNTIF('2010'.D:D,"Yellow")+COUNTIF('2017'.D:D,"Yellow")
I haven’t checked, but suspect that concatenating two full columns may create a range too large for the function to handle. Concatenating more limited ranges from several sheets seems to work with COUNTIF().
Will you ever use all the rows down to #1048576? With that amount of data, you may be better off using a database tool.
Hello,
by meaningless result, I meant an integer like 23, but I don’t know how it got calculated.
Your code returns me the number of the two sheets 2010 and 2017, and that’s what I’m trying to avoid, concatenating, in my case, 8 countifs, cause the next years follow…
Your code returns me the number of the two sheets 2010 and 2017,
And what number is that?
Your code found all the “Yellows” of sheet 2010 and 2017 and summed them correctly.
As expected:
Your code found all the "Yellows" of sheet 2010 and 2017 and summed them correctly.
And what number was that?
And what is that “meaningless” number your formula returned?
Are we bringing sense into the case now?
With the tilde you concatenate two ranges from distinct sheets - first and last - to a new range. What you want, according to comments to the question, is to specify the full range of cells on all sheets - first to last. From-to range operator is colon.
This might have worked:
=COUNTIF('2010'.D:'2017'.D,"Yellow")
However, it doesn’t at this time. Sorry that I posted this as a solution, misleading you.
Many functions which operate on ranges do not support ranges across multiple sheets. One notable exception is SUM(). I do not know any other …
So, the workaround for your current data is to name each range explicitly, either by your approach of range concatenation, or mine with separate COUNTIF() for each range. Which one to use is up to you. Most likely there is little difference. I would guess that the former is marginally faster but I find the latter easier to read.
I do not know your exact layout, so for your case it may make sense, but subdividing source data to sheets by year this is usually a bad idea. It makes summaries much harder to create, and increases the potential for error.
Best practice is to have one large table for all data of the same type. Instead of creating a new sheet for a new year, add a field to indicate year for subdivision (if you already have a date field, year can be extracted from that). With that, it is easy to create the subdivisions as needed. You can have just one sheet, and enter the year to extract relevant data.
It would be good if you actually tested your solutions; COUNTIF() does not handle 3D ranges.
That’s what I thought, so I did. Worked for me. Checking again…
Hello, I just tried your code and get an Err:504 as a result.
Yes, erAck is right of course. Many functions, including COUNTIF(), do not work with ranges across multiple sheets. My tests seemed to indicate that Calc had improved in this respect, but I confused my test formulas, and lied to you. I apologize!
This brings us back to “best practices”: don’t divide data to multiple containers if you want to summarize. Use an identifier field (year column in this case) instead, and rather extract with a filter from that big table when you need subdivision.
Editing my answer, adjusting “solution” to “workaround” and also my lie is revealed…
No need to apologize keme, thanks for your effort…
No, I think it’s rather sad that something that simple is that troublesome in 2019 
Hi, I have a very similar sheet as you, only mine is monthly currently I have 20 completed months and 1 open, I have created a summary sheet, which looks at the individual monthly sheets and produces a summary counts and totals. Using your data as an example, I have used just a couple of years and a few colours to produce a summary sheet. Have a look and let us know if it helps.
LOQ_20191027.ods
Hello gregors15, in your file you tabularize everything in the first place and then you are summing up everything. That’s the other way around :). You have not one single code, but a nice summary instead, thanks for your file!.
Hello there,
I don’t know if this question is still open, so sorry for potentially spamming all of you.
I have no direct solution, but maybe a useful workaround.
Since I’ve messed up a lot of data lately, I had to notice the opportunity to clone inputs on multiple sheets.
Furthermore, someone earlier mentioned, the sum-function works across a range of sheets.
Combining this, I suggest implementing a statistical header in all of your sheets at the same time by marking them (strg+shift+ pg up/down), creating new lines, and inserting the countif() functions.
To generate your output, you can use the sum-function across all the sheets you need (checked it for min & max as well - it works)