Match criteria THEN CountIFs

Question

I am trying to create a formula in Google Sheets for a list of account names.

In Sheet1 I have a product list where each row contains a product, its status,m and associated account.

In another sheet I'm trying to write a formula that will perform a lookup based on a list of accounts, and return for each a count of associated products that also contain "apple"** in the product name and where the status is not "beta"

I've tried a few approaches but none work:

This returns an error:

=COUNTIFS(filter('Sheet1'!A:A, A1)'Sheet1'!A1,'Sheet1'!G:G,"* apple *",'Sheet1'!F:F,"<>beta")

This is not accurate, for example it returned 2 when I can see that it should be 11

=COUNTIFS('Sheet1'!A:A, A1,'Sheet1'!A1,'Sheet1'!G:G,"* apple *",'Sheet1'!F:F,"<>beta")

This also is not accurate, returning 0 when it should be 1

=COUNTUNIQUEIFS('Sheet1'!A:A, A1,'Sheet1'!A1,'Sheet1'!G:G,"* apple *",'Sheet1'!F:F,"<>beta")

What is the correct formula?

Answer 1

QUERY Function

=QUERY(Sheet1!A:G, "Select A, COUNT(A) 
   Where F != 'beta' AND  G matches '.*apple.*'
   Group by A Label COUNT(A) 'Count'",1)