How to properly rewrite an Excel formula to parse filtered rows for Google Docs tables?

Question

Google Sheets

There is a table on sheet "Sheet1". The table consists of two columns A and B:
A
1. AorB
2. A
3. A
4. B
5. B
6. A
7. A
B
1. Sex
2. Male
3. Male
4. Female
5. Female
6. Female
7. Shemale
On Sheet2 of the table there is a formula that processes the data after the user has filtered the desired gender on the first sheet by column B (it displays the number of answers A in the rows output by the filter). In Excel it goes like this:

=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ(Лист1!A2;СТРОКА(Лист1!A2:Лист1!A2000)-СТРОКА(Лист1!A2);))*(Лист1!A2:Лист1!A2000="А"))

or like this:

=СУММПРОИЗВ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ(Лист1!A2:Лист1!A2000;СТРОКА(Лист1!A2:Лист1!A2000)-СТРОКА(Лист1!A2);0;1));--(Лист1!A2:Лист1!A2000="А"))

But in Google docs, such constructions do not work, although they are converted into something similar during import, but it does not work:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Лист1!A2,ROW(Лист1!A2:A2000)-ROW(Лист1!A2),))*(Лист1!A2:A2000="A"))

I can't figure it out for days! I rewrote it in different ways, I used ArrayFormula() but I didn't get the desired result...

Answer 1

In Google Sheets "Filter" does not match the "Filter" function in MS EXCEL. The process needs to be changed.