How to count only true values of a column when filter other columns

[gm91]

Hi all. I have an excel sheet with the following columns:

COLUMN A	COLUMN B
TRUE	PREMIER LEAGUE
FALSE	CHAMPIONS LEAGUE
TRUE	LIGUE 1
FALSE	PREMIER LEAGUE
FALSE	CHAMPIONS LEAGUE
FALSE	PREMIER LEAGUE
TRUE	LIGA

I would to create a cell C1 with the percentage of true values for each filtered value of column B. So, if I filter with 'PREMIER LEAGUE' value in column B I want the percentage of true values for this occurrence. It is possible in excel?

 

[eirikdaude]

@eirikdaude The syntax is now correct but it continues to work only if I don't use filters on column B

It worked for me when I filtered the table. Kinda stumped why it doesn't for you.

 

[aghaffar82]

Have a look, the values are filtered and it is working perfectly fine

 

[gm91]

Try this to get the percentage of True Values:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A8,ROW(A2:A8)-MIN(ROW(A2:A8)),,1))*((A2:A8)=TRUE()))/SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A8,ROW(A2:A8)-MIN(ROW(A2:A8)),,1)))

Unfortunately it doesn't work well. In case no filter is applied on column B it works fine, but in case there is a filter on column B it calculates the divisor always equal to 0.

 

[Fluff]

@gm91
Are you doing this in Excel, or some other app?

 

[aghaffar82]

@gm91
Are you doing this in Excel, or some other app?

I did on Excel (Version 365) and as you can see I have applied the filter on column B and it is working perfectly fine.

 

[Fluff]

I was talking to @gm91

 

[gm91]

@Fluff I tried it on excel and it works. At first I was trying on google sheets and it doesn't work. I do not understand why

 

[Fluff]

Sheets & Excel are not the same, whilst there are a lot of similarities not everything works.
In future, if you need a solution for Sheets rather than Excel, you should post in the "General Discussion & Other Applications" sections & clearly state that you are using sheets.
Thanks.

 

[gm91]

Sheets & Excel are not the same, whilst there are a lot of similarities not everything works.
In future, if you need a solution for Sheets rather than Excel, you should post in the "General Discussion & Other Applications" sections & clearly state that you are using sheets.
Thanks.

Ok thanks

 

[Fluff]

Glad we could help & thanks for the feedback.