Excel COUNTIFS filtered

Question

I am trying to extract specific filtered data.

Example Unfiltered table:

    A       B       C
1  Ext   Room 1   Present
2  Ext   Room 1   Present
3  Int   Room 1   Present
4  Int   Room 2   Present
5  Int   Room 2   Present

So if I want to get the Number of "Int" "Present" I would do:

=COUNTIF(C1:C5;"Present";A1:A5;"Int")

And the answer would be 3

Now if I filter the rooms and select only room 1 I will get this filtered table:

    A       B       C
1  Ext   Room 1   Present
2  Ext   Room 1   Present
3  Int   Room 1   Present

Since its a filtered table now the same formula doesn't work. Gives me the previous amount instead of 1 since there is only 1 "Int" "Present" now.

I tried to do SUMPROD with SUBTOTAL and COUNTIF but the result still isn't showing properly.

Anyone has a different formula that will allow me on this filtered table get what I want. On this case: "Int" "Present".

Bests.

Answer 1

Found the answer using OFFSET and ROW options in the SUMPROD instead of countif. First comment edited.

=SUMPRODUCT(SUBTOTAL(3;OFFSET(C1;ROW(C1:C5)-ROW(C1);0));(A1:A5="Int")+0)