How to summarise dependent totals on a single data set.

This is probably an FAQ so apologies.
I can do all the below in PowerShell by using Group object, sort object, select object and the pipeline, but excel is a program I don't know well so I am struggling to nest output arrays together as I would in PowerShell [probably this completely the wrong way to conceptualise the solution in excel].

This is the overview of what I am after.

I have a set of data (a MS active directory export) and I am looking to summarise aspects of that data set in adjacent columns on a separate tab in excel.
Something like a total count of a given AD container and then in the next column 23 of that total are blue and of those 23 blue 4 are straight and of those 4 straight 2 are glued and the other 2 are rough. if I can describe that in excel formulas, I can solve all my display requirements.
The point is that some columns depend on referencing the members of the previous columns output and then re-querying the original dataset to evaluate other columns of that data and totalling it. I realise that the grouping from a previous column will be lost inside the formula since I only want to display the total in each case.

In more specific detail:

I need to summarise different elements of all the users in each OU in scope, there are about 90 OU's with 35000 users.
1. Initially I want a column (sorted descending) with the count of users in each OU - some have 25 and others 10000 or so.
2. Then I want a adjacent column with the number of users in each of those OU's which have a password older than three years - (which is less than for excel since it will be a smaller integer between that date and 01/01/1900 or whenever excel starts from.)
- So this is a dynamic array selection on the main data set passed to a "countif date is less than" in my head - but I can't get that to work. . . so.
3. I need to also display in the next column how many of the accounts in this OU meet another criteria.
- So this needs to reference the cells returned in #2 and then query another column for text "TRUE" or "FALSE" or other conditions.

I think those cover off the three types of things I need to do.

To recap all queries against the 35000 rows of the original data need to be constrained by the Organisational Unit [Ad container]. Some future queries depend on (reference) a collection [dynamic range] from a previous query and interrogate other columns using that [dynamic] range as the row references.

Is this simply too complex for excel or if not is it better done with a pivot table or a bunch of specific queries and helper tabs / cells if needed?

I have attached a sample 10 rows of data [obviously not real data] to demonstrate how it is organised.

Thanks in advance.

Sorry the version is listed against my name and in my profile ? It is licenced as 2016, but I think it is actually 365 as it updates regularly. Is there somewhere else this information needs to be placed other than in my BIO at top left ?

Many thanks Ali, let me try to digest that !

I am finding that once I "allow editing" on the example workbook attached to the thread - all the values change to #NAME? - they preview fine though.

Attachment 867450
Attachment 867451

Also when I type "=Let(" no function is found and applied. So perhaps the functionality of my version of excel is limited to 2016 even though its 2403 ?

I get this (Its for outlook but the version and logo are the same.

13.jpg

No - it's all Office 2016 Click to Run - what's going on is the functionality of the licence I have (2016) is being imposed onto the M365 build I am running - that's how they do it. So unless I start subscribing I will not unleash the extra functionality that lies within the 2043 binaries on my disk.


C:\Program Files (x86)\Microsoft Office\Office16>cscript ospp.vbs /dstatus
Microsoft (R) Windows Script Host Version 5.812
Copyright (C) Microsoft Corporation. All rights reserved.

---Processing--------------------------
---------------------------------------
PRODUCT ID: 00338-95670-06701-AA296
SKU ID: de52bd50-9564-4adc-8fcb-a345c17f84f9
LICENSE NAME: Office 16, Office16ProPlusR_Retail edition
LICENSE DESCRIPTION: Office 16, RETAIL channel
LICENSE STATUS: ---LICENSED---
Last 5 characters of installed product key: xxxx
---------------------------------------
---------------------------------------
---Exiting-----------------------------

Build data as per the bitmap previously.

Did your other post mean to say you no not like pivot tables ?

Other matters have been consuming my time - thank you for the help and the suggestion. I think that might be simpler to implement as I can constrain it to a dynamic range on the OU.