SUM COUNTIF Statement Issue

[MattH1]

I'm looking to create a query to give me a count of the following subset of data:

It must be "HA" , "HB", or "HC" or "HD" in Column A.
It must be "Slide 4" or "Slide 5" in column B.
It must be "AX123" in Column C.
It must be "T" or "T-1" on Column D.

When I made the "SUM(COUNTIFS(..." query, I seemed to run into a comma/semi-colon issue. Below was the formula I made and it's not giving me the right value.

=SUM(COUNTIFS($A:$A,{"HA","HB","HC","HD"},$B:$B,{"Slide 4";"Slide 5"}, $C:$C,"AX123",$D:$D,{"T";"T-1"}))

Any help is truly appreciated.

Thanks,
MH

 

[Jonmo1]

You can't do more than 2 sets of multicriteria arrays with the sum(countif syntax.

I recommend helper columns for each of the multicriteria.

in E2, =OR(A2={"HA","HB","HC","HD"})
in F2, =OR(B2={"Slide 4""Slide 5"})
in G2, =OR(D2={"T""T-1"})
Fill those down as far as needed.

Then
=COUNTIFS(E:E,TRUE,F:F,TRUE,G:G,TRUE,C:C,"AX123")

 

[MattH1]

You can't do more than 2 sets of multicriteria arrays with the sum(countif syntax.

I recommend helper columns for each of the multicriteria.

in E2, =OR(A2={"HA","HB","HC","HD"})
in F2, =OR(B2={"Slide 4""Slide 5"})
in G2, =OR(D2={"T""T-1"})
Fill those down as far as needed.

Then
=COUNTIFS(E:E,TRUE,F:F,TRUE,G:G,TRUE,C:C,"AX123")

Haha well that would would explain why adding my last criteria ruined it all. I though the T and T-1 should be changed in some way.

Thank you - I'll look into helper columns or just change my SQL query from access to only pull in T and T-1 items from the start.

 

[Jonmo1]

You're welcome.