First, a confession. I was searching the internet for a workaround to my own requirement and this (originally Excel) looked like a potential solution so I just copied the sample with the intention of making appropriate modifications.
What intrigues me is that what I believe to be the parameter for COUNT() in the SUBTOTAL() function actually returns the SUM().
This is the original - I needed to find out how to get the COUNT() before any further experiments with replacing my own array and targeting.
If I can get this to work it replaces 12000+ (growing by 800 monthly) active cells with just 5 array functions
Anybody got any ideas?
SUMfor COUNT.ods (16.0 KB)
Hallo
the Usecase for SUBTOTAL is in the Context of filtered Data, and your self-crafted Formula is complete Nonsense.
use SUBTOTAL simply as designed for Filtered Data, or better the →Pivottable
Subtotal_or_pivot_example.ods (16.7 KB)
Which bit of “…I just copied the sample…” or “This is the original…” didn’t you understand? I certainly understand which part of your response is intended to be insulting.
My file with “…12000+ active cells…” contains more SUBTOTALS() than you can shake a stick at, but they don’t provide the full functionality I seek. I desire the functionality of an ARRAY process that doesn’t fail miserably to implement a SUBTOTAL(COUNT()) on filtered arrays.
Perhaps I should have mentioned - not one of my data elements is money. As I clearly stated - this is a sample I copied that I thought might provide the basis for experimentation but I couldn’t understand why the COUNT() function was providing a SUM() return.
Did I not explain that clearly enough? The clue was in the header.
Was it so difficult to comprehend?
Please could you also explain to a confused user, just which of the elements in your example provides the SUBTOTAL(3,COL(X)) in an ARRAY function? That would be one of those ARRAY functions defined by CTRL+SHFT+ENTER
EDIT: the 12000+ are only the cells I wish to replace with an ARRAY process, the entire file has 4 sheets, 51003 cells, 143 pages and 1428 Formula groups
I’m annoyed by the claim “SUBTOTAL doesn’t work” … how can you judge that when you hide the function in a nightmare of SUMPRODUCT, OFFSET, ROW ectpp. that you can’t even begin to comprehend?
Do your self an favour and use for Example →Pivottable and|or some simple Formula… NOT THAT OVERHELMED crap.
Your words, not mine. I asked if anybody could explain why subtotal,3 which is supposed to return the count was returning the sum. I also explained - twice now - that I didn’t write the OVERHELMED crap but you still wish to cast aspersions. Perhaps you should look at the other guy’s original sheet which I attached above and instead of getting your panties in a bunch you simply type the word “East” or “North” in B1. Wow, look at that it makes the pivot table redundant. Wonderful news, I never ever wanted a pivot table. I investigated and discarded those eons ago - they don’t suit my purpose. If they did I would possibly be asking for help with pivot tables. This is the one I experimented with and it is not suited to my requirements.
pivot.ods (23.9 KB)
Now focus, do you know why SUBTOTAL(3,…) returned the SUM() instead of the COUNT()? If(yes;please elucidate;“NO”)
Reset for christmas?
I’m more than happy to send you the entire file for my project and discuss the precepts. It has grown over the last 14 months and I have “improved” it immensely. I just constantly use it as a testbed to experiment with new ideas and hopeful improvements, whereby such things as 12000+ helper cells with active formulae can be replaced with five ARRAY formulae and when I get that to work, try to make it one ARRAY formula.
Hint: You can call somebody a noobie and demonstrate a better solution - that’s taken as humour, but never tell somebody their effort is complete nonsense when they have already identified that it isn’t theirs and they’re asking for advice.
EDIT: Correction, it was ROOKIE - not noobie 
=SUBTOTAL(3;B6:B16)
works exactly as it should,(you would realize that if you look into my replied Calcdocument)… but if framed it into a sophisticated SUMPRODUCT( ………) it simply returns the same SUM as:
=SUMPRODUCT(A6:A16=B1;B6:B16)
I’m fully aware of the syntax and return for SUBTOTAL() and that the parameter 3, returns the COUNT() but if you look at the original OVERHELMED example you’ll notice that the SUBTOTAL 3, returns the SUM() and my question was - Why? Shouldn’t it be a 9 to get the SUM? Also, why does anything other than 3 as the parameter in the original always return zero?
What I was keen to replicate was the ability to type South and the function was then constructing the query to pull the data for South I just didn’t want to SUM() it, I wanted to COUNT() it. As I said, there’s no money anywhere in my sheet. I’m not even interested in what the target number maý be - just how many times the number I specify has appeared in the data in that specific column. My current setup allows me to identify which column and define whether I want a count of <, <=, =, >= or > say 0.6. That is achieved by having helper columns that register true or false for the event in the corresponding data column. Those are then counted with SUBTOTAL(3,) and the result provided to my enquiry. There are 5 “real” columns each with 3500 active cells and I was hoping to use an array function to obviate the “duplication” of the helper columns.
I’ve just had another idea - a possible experiment with frequency analysis because I only really need to count <, =, > and can extrapolate the other two values. If frequency analysis can be instructed to ignore “hidden” cells and allows me to dynamically define a precise target then just allocates < & > to the two surrounding cells that’s the result I’m after. Maybe no experiments are needed if you already know whether frequency can ignore hidden cells and be given just 3 variable classes - well, one class with everything over or under being considered as the other two classes.
No it doesnt…it produce in company with the itself SOPHISTICATED OFFSET-function an array of zeros and ones,
see the isolated function result in upper|right corner
at this point its EOT for me …… why the hell should I explain to you, functions that YOU have chosen for yourself? And continue to ignore hints of better simpler solutions:
a typical statement from notorious ignorants!
How do you do that? This is what I get from the function wizard and no matter how/where I manipulate the highlighting of the formula in its window or my focus in the structure, it never changes.
Also, and perhaps it should have been my first question, is there a modification to the function that does give the count()? I still haven’t quite got my head around how that series of binaries ultimately equates to 7673,85 and would love it to say “4”
But they don’t provide the targeted subtotal count on a filtered array which is what I’m trying to achieve.
Ignorant is what ignorant does. Would you disagree?
I step through the Formula with next|back-button.
Good to know! I thought it was related to your highlighting of the function component so couldn’t replicate it.
Now all I need do is get it to say “4” - then I’ll have learned two new things.
Just to clearify: OFFSET(A6:A16;……; 1) return 10 different entrys, and the outer SUBTOTAL(3;…) is evaluated 10times
If it’s accumulating all those binary indicators and evaluating everything for the number of rows in the array, that probably implies it would develop indigestion on an array of 3500+ rows - even if I could get it to count instead of sum.
That’s the convincing argument to abandon the approach. My current process just does it once per row and I thought array processing would reduce that volume.
That’s the second thing I’ve learned today.
thats also FALSE, Pivottables “react” on Filter-settings in the Sourcerange, …and provides itself Filter-options on every “corner”, and of course its a matter of ~3Mousclicks to aggregate with COUNT (additionally or instead) SUM
of course, remove the “tail”:
=SUMPRODUCT((SUBTOTAL(3;OFFSET(A6:A16;ROW(A6:A16)-MIN(ROW(A6:A16));;1))))
but at this state the outer SUMPRODUCT(…) is superfluous, and at the End of the Day we come back to simply:
=SUBTOTAL(3;B6:B16)
But with this I can’t say count only the items >72,3 in an array that isn’t already filtered to exclude those <=72,3
Switch from some Filter to →→Standardfilter, set your desired Options and youre done.
The whole point of my exercise is that I don’t want to keep manually changing filters to “exclude” from the display what I don’t want to count.
I want to say "how many records in column L are greater than 73,5 - just tell me the number - Oh, by the way, I’ve now filtered out everything that occurred in February so the new count is automatic. Hmmm, interesting, how many in column N are now at -0,4 and how does that change if I add February back but remove July - and about 3500^5^6 other permutations. All of which I can do with the 5 extra columns of helper cells which monitor the values on a row by row basis and which I was hoping to replace with an array function. What would be perfect would be subtotal(3, IFANDOR(IFANDOR(IFANDOR - with all the factors being cell lookups of such operators as <,<=,=,>=,>, Val(x),Col (L).
I do realise this is an extreme/advanced “single” function but it would be replacing thousands of active cell formulae - if it were possible. I think it’s probably more akin to a user-defined function in Excel’s LAMBDA().
I currently achieve it with three “source” cells using an HLOOKUP() to define which column’s filter SUBTOTAL(x,) will contain the result. It does what it says on the box but I was experimenting to see if it could be “streamlined” with an array function to remove the 15000 helper cells and work directly upon the “real” data cells.
I think the function name is probably MIRACLE().