Counting only visible entries in a filtered table

[Chalklands]

Hello,
I have a formula that calculates Deals 'Won'' as a % of Total Deals ('Won'+'Lost').

[TABLE="width: 100"]
<tbody>[TR]
[TD]=IFERROR((COUNTIF(Tbl_Deals_1[Status],"WON")/SUM(COUNTIF(Tbl_Deals_1[Status],"WON"),COUNTIF(Tbl_Deals_1[Status],"LOST"))),"")[/TD]
[/TR]
</tbody>[/TABLE]

It works fine, but I want it to only calculate for visible rows in a filtered table (ignoring hidden rows). Can anybody help me with this please?

Thanks

Pete

 

[Marcelo Branco]

Try

=IFERROR(SUMPRODUCT(--SUBTOTAL(3,OFFSET(Tbl_Deals_1[Name],ROW(Tbl_Deals_1[Name])-MIN(ROW(Tbl_Deals_1[Name])),0,1)),--(Tbl_Deals_1[Status]="WON"))/(SUMPRODUCT(--SUBTOTAL(3,OFFSET(Tbl_Deals_1[Name],ROW(Tbl_Deals_1[Name])-MIN(ROW(Tbl_Deals_1[Name])),0,1)),--(Tbl_Deals_1[Status]="WON"))+SUMPRODUCT(--SUBTOTAL(3,OFFSET(Tbl_Deals_1[Name],ROW(Tbl_Deals_1[Name])-MIN(ROW(Tbl_Deals_1[Name])),0,1)),--(Tbl_Deals_1[Status]="LOST"))),"")

M.

 

[Marcelo Branco]

Sorry, i used the column name of my test table.
Disregard the formula above

Try
=IFERROR(SUMPRODUCT(--SUBTOTAL(3,OFFSET(Tbl_Deals_1[Status],ROW(Tbl_Deals_1[Name])-MIN(ROW(Tbl_Deals_1[Status])),0,1)),--(Tbl_Deals_1[Status]="WON"))/(SUMPRODUCT(--SUBTOTAL(3,OFFSET(Tbl_Deals_1[Status],ROW(Tbl_Deals_1[Status])-MIN(ROW(Tbl_Deals_1[Status])),0,1)),--(Tbl_Deals_1[Status]="WON"))+SUMPRODUCT(--SUBTOTAL(3,OFFSET(Tbl_Deals_1[Status],ROW(Tbl_Deals_1[Status])-MIN(ROW(Tbl_Deals_1[Status])),0,1)),--(Tbl_Deals_1[Status]="LOST"))),"")

M.

 

[Marcelo Branco]

If you want to know how this kind of formula works, see:
https://exceljet.net/formula/count-visible-rows-only-with-criteria

Or the topic Countif Filtered Data With Criteria By Excel Functions at
https://www.extendoffice.com/documents/excel/2519-excel-countif-filtered-data.html

M.

 

[Marcelo Branco]

OMG correction

=IFERROR(SUMPRODUCT(--SUBTOTAL(3,OFFSET(Tbl_Deals_1[Status],ROW(Tbl_Deals_1[Status])-MIN(ROW(Tbl_Deals_1[Status])),0,1)),--(Tbl_Deals_1[Status]="WON"))/(SUMPRODUCT(--SUBTOTAL(3,OFFSET(Tbl_Deals_1[Status],ROW(Tbl_Deals_1[Status])-MIN(ROW(Tbl_Deals_1[Status])),0,1)),--(Tbl_Deals_1[Status]="WON"))+SUMPRODUCT(--SUBTOTAL(3,OFFSET(Tbl_Deals_1[Status],ROW(Tbl_Deals_1[Status])-MIN(ROW(Tbl_Deals_1[Status])),0,1)),--(Tbl_Deals_1[Status]="LOST"))),"")

 

[Chalklands]

Thank you Marcelo, It works perfectly.

Pete

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[Chalklands]

OMG correction

=IFERROR(SUMPRODUCT(--SUBTOTAL(3,OFFSET(Tbl_Deals_1[Status],ROW(Tbl_Deals_1[Status])-MIN(ROW(Tbl_Deals_1[Status])),0,1)),--(Tbl_Deals_1[Status]="WON"))/(SUMPRODUCT(--SUBTOTAL(3,OFFSET(Tbl_Deals_1[Status],ROW(Tbl_Deals_1[Status])-MIN(ROW(Tbl_Deals_1[Status])),0,1)),--(Tbl_Deals_1[Status]="WON"))+SUMPRODUCT(--SUBTOTAL(3,OFFSET(Tbl_Deals_1[Status],ROW(Tbl_Deals_1[Status])-MIN(ROW(Tbl_Deals_1[Status])),0,1)),--(Tbl_Deals_1[Status]="LOST"))),"")

How would I add another criteria to the formula? I have a column called 'Stage' that has either ' Meeting with customer' or 'Tested with Customer' as the available options. I'm trying to calculate the percentage of deals that are lost at the 'Meeting' stage and the 'Tested' stage?

 

[Marcelo Branco]

To use an OR condition you should add the two conditions. Try something like
--((Tbl_Deals_1[Stage]="Meeting with customer")+(Tbl_Deals_1[Stage]="Tested with Customer"))

M.

 

[Chalklands]

To use an OR condition you should add the two conditions. Try something like
--((Tbl_Deals_1[Stage]="Meeting with customer")+(Tbl_Deals_1[Stage]="Tested with Customer"))

M.

Thanks for all your help Marcelo. I actually managed to work it out myself by reading the link to 'exceljet' you sent earlier.