Countifs function with Array/table and multiple criteria

[Amstercam]

I am trying to count the number of "False" cells in a table, if the rows and columns are both labelled as "Active").
Have tried countifs formula but cant get it to work.

 

[hagia_sofia]

How about:

=LET(a,HSTACK(A2:A8,FILTER(B2:F8,B1:F1="Active")),b,FILTER(a,CHOOSECOLS(a,1)="Active"),SUM(--(b="FALSE")))

 

[Cubist]

Try:

=SUM(--IF(A2:A8&B1:F1="ActiveActive",NOT(B2:F8),0))

 

[Cubist]

A shorter version.

=SUM(--(A2:A8&B1:F1&B2:F8 = "ActiveActiveFALSE"))

 

[Amstercam]

How about:

=LET(a,HSTACK(A2:A8,FILTER(B2:F8,B1:F1="Active")),b,FILTER(a,CHOOSECOLS(a,1)="Active"),SUM(--(b="FALSE")))

Couldnt get this to work in Google Sheets but appreciate it

 

[Amstercam]

A shorter version.

=SUM(--(A2:A8&B1:F1&B2:F8 = "ActiveActiveFALSE"))

Thank you. FYI in Google Sheets I needed to wrap this in an Arrayformula() function but it works perfectly.

 

[RoryA]

You probably should have mentioned it was for Sheets; this isn’t the area for those questions.