COUNTIFS with Multiple Ranges and Criteria

Question

EDIT: I completely re-wrote the question to try and be less obtuse.

I have a password audit report that contains usernames, LM hashes, NTLM hashes, and columns for the status of whether the LM/NTLM password hash was cracked or not. I am interested in finding accounts with the string "admin" that have been cracked. Below is the original equation I tried but it does not work.

=SUM(COUNTIFS(Tbl_City[Username], "=*admin*", {Tbl_City[LM State], Tbl_City[NTLM State]},{"1st Half Cracked", "2nd Half Cracked", "Cracked (Brute:Fast)*"}))

It gets tripped up on the second criteria range - Seems it doesn't like the multiple ranges.

What I'm looking for is "IF Username contains "admin" AND ((IF LM State = "1st Half Cracked" OR "2nd Half Cracked" OR "Cracked (Brute:Fast)") OR (NTLM State = "Cracked (Brute:Fast)") THEN count it.

This equation works but only checks the first column:

=SUM(COUNTIFS(Tbl_City[Username], "*admin*", Tbl_City[LM State], {"1st Half Cracked","2nd Half Cracked","Cracked (Brute:Fast)*"}))

My apologies for the initial confusion and thank you for your help.

Essentially. Though in thinking about this more there is never an occasion where B would be false but C would be true. It would either always be both true or both false, or B true and C false. With that being the case I think I can simplify my equation considerably. — Tchotchke
You're using COUNTIFS, as opposed to SUMIFS, which would seem to contradict your statement "and if found it'll SUM it if B or C equals a specific value". Also, are val1, val2 and value all numerical? — XOR LX
You're right, I am misusing my words. I intend COUNTIF, not SUMIF. All these fields are text fields. I am working with password audit data. Column A are usernames, Columns B and C are LM Status and NTLM Status of whether a password was cracked or not. I am trying to find usernames that contain the string "admin" and then determine if the password was cracked. Because of how LM hashes are created it is possible to crack the first or second half of the hash, or the entire thing. Therefore, Column B could say "1st half cracked", "2nd half cracked", "cracked". — Tchotchke

Alexander Frolov Alexander Frolov · Accepted Answer · 2016-11-23T12:39:37

If I understand your right, please use the following array formula (remember to press Ctrl+Shift+Enter):

=SUM((--ISNUMBER(SEARCH("value",$A1:$A10)))*(IF(((--($B1:$B10="val1"))+(--($B1:$B10="val2"))+(--($C1:$C10="val1"))+(--($C1:$C10="val2")))>0,1,0)))

Where

$A1:$A10 - Tbl_City[Name]

$B1:$B10 - Tbl_City[X]

$C1:$C10 - Tbl_City[Y]

more about AND and OR operators in Excel array formulas

COUNTIFS with Multiple Ranges and Criteria

1 Answers