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]

Does

=COUNTIF(A1:A7;TRUE())/COUNTA(A1:A7)

give you the result you expect?

 

[hrayani]

Try:

=COUNTIFS(A2:A8,"true",B2:B8,"premier league")/COUNTIF(B2:B8,"premier league")

 

[gm91]

@hrayani Unfortunately, this formula doesn't work. Among other things, it must be dynamic (otherwise it filters only by 'PREMIER LEAGUE' value. So if filtering for example by 'LIGA' value no longer works)

 

[eirikdaude]

There's a recipe on how to SUMIF only visible cells here. Hopefully you can make it work with your problem too?

 

[gm91]

@eirikdaude I have used the recipe of the article in this way:

=SUMPRODUCT((SUBTOTAL(3,OFFSET(B2:B,ROW(B2:B)-MIN(ROW(B2:B)),,true)))*(A2:A=true))

but dosn't work very well (works well when the filter is not applied on column B. When the filter is applied it does not work). Where am i wrong?

 

[eirikdaude]

Tbh, it looked like an absolute pain to figure out and implement, so I was hoping you'd figure it out from the thread @gm91 but it looked like the OP in that post obtained their objective if you look at the end of the thread.

I'll have a closer look on it and see if I can figure it out, but it might take a little while

 

[eirikdaude]

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

@gm91 Does this do the job as expected? You'll have to replace the semicolons with commas if that is your argument separator.

 

[gm91]

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

@gm91 Does this do the job as expected? You'll have to replace the semicolons with commas if that is your argument separator.

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

 

[aghaffar82]

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)))