How can I aggregate fields based on the value of another field?

I need countif(name...) to also be distinct by Identifier.

If I understood your intention correctly, you could use dcountif().

For example:

customEvents
| where timestamp > ago(1d)
| extend Identifier = tostring(customDimensions["Identifier"])
| summarize TotalUserCount = dcount(Identifier),
            SuccessCount = dcountif(Identifier, name startswith "Success"),
            FailureCount = dcountif(Identifier, name !startswith "Success")

The number of failed login attempts that preceded a successful attempt (if any) - is this even possible in KQL?

You could try using the scan operator for this: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/scan-operator

I would start from analyzing the data in the session level.
It's very easy to take it from there and summarize it to the user level etc.

// Data sample generation. Not part of the solution
// Setup
let p_event_num                     = 30;
let p_identifiers_num               = 3;
let p_max_distance_between_events   = 2h;
let p_names                         = dynamic(["Unsuccessful login. Invalid cred", "Unsuccessful login. Account wa", "Successful login"]);
// Internal
let p_identifiers   =   toscalar(range i from 1 to p_identifiers_num step 1 | summarize make_list(new_guid()));
let p_names_num     =   array_length(p_names);
let customEvents    =   materialize
                        (
                            range i from 1 to p_event_num step 1 
                            |   extend  ['timestamp [UTC]'] = ago(24h*rand())
                            |   extend  Identifier          = tostring(p_identifiers[toint(rand(p_identifiers_num))])
                            |   extend  name                = p_names[toint(rand(p_names_num))]
                        );
// Solution starts here
customEvents
|   project-rename ts = ['timestamp [UTC]']
|   partition hint.strategy=native by Identifier
    (
            order by    ts asc
        |   extend      session_id          = row_cumsum(iff(ts - prev(ts) >= p_max_distance_between_events, 1, 0))
        |   summarize   session_start       = min(ts)
                       ,session_end         = max(ts)
                       ,session_duration    = 0s
                       ,session_events      = count()                        
                       ,session_successes   = countif(name  startswith "Successful")
                       ,session_failures    = countif(name !startswith "Successful")
                       ,arg_max(ts, name)
                        by Identifier, session_id
    )
|   project-away    ts
|   project-rename  session_last_name = name    
|   extend          session_duration = session_end - session_start
|   order by        Identifier asc, session_id asc   
|   as              user_sessions

Fiddle