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().
Did you view the file? Is there a way to get it into ASKLo or do I have to leave the link to my GDrive in perpetuity?
The usecase for SUBTOTAL is about using Filters, if you want to not use Filters at all, or in humptie combinations with other Criteria, you should not use SUBTOTAL, but something of SUMIF[S], COUNTIF[S], SUMPRODUCT.
Sorry I get the Audio-part only, no video!