SubTotal

[MARKEGANDERSON]

Hello All-

I want to count all the "Yes", in a data range define as "CPR_Data", using the SubTotal function.

Can anyone help?

 

[Logit]

.

=COUNTIF(A2:N10,"Yes")

 

[MARKEGANDERSON]

thanks - This will count all the "YES" in "CPR_Data". I wanted it to count all the "YES" that's filtered. Reason why I wanted to use the subtotal function.

So combining your formula with the subtotal function would work, but I don't know how

 

[Eric W]

If CPR_Data is a single column, then:

=SUM(IF(CPR_Data="yes",IF(SUBTOTAL(103,OFFSET(CPR_Data,ROW(CPR_Data)-ROW(INDEX(CPR_Data,1)),0)),1)))

with Control+Shift+Enter.

 

[MARKEGANDERSON]

Thanks Eric - it gives me a #REF error

 

[Eric W]

What are the dimensions of CPR_Data?

 

[MARKEGANDERSON]

Column T

=Data!$T:$T

 

[MARKEGANDERSON]

I replace the defined name CPR_data with T1:T9812, it works but counting everything in that range even if it is filtered. So Arrival week of 13 AUG has 13 "Yes" in column T. But when I filter by Week of 20 Aug which has 11 "Yes", it gives me a total of 24.

=SUM(IF(T1:T9812="yes",IF(SUBTOTAL(103,OFFSET(T1:T9812,ROW(T1:T9812)-ROW(INDEX(T1:T9812,1)),0)),1)))

 

[Aladin Akyurek]

I replace the defined name CPR_data with T1:T9812, it works but counting everything in that range even if it is filtered. So Arrival week of 13 AUG has 13 "Yes" in column T. But when I filter by Week of 20 Aug which has 11 "Yes", it gives me a total of 24.

=SUM(IF(T1:T9812="yes",IF(SUBTOTAL(103,OFFSET(T1:T9812,ROW(T1:T9812)-ROW(INDEX(T1:T9812,1)),0)),1)))

You need to confirm this formula with control+shift+enter.

The SUMPRODUCT array-processing equivalent which does not require control+shift+enter is...

=SUMPRODUCT(SUBTOTAL(103,OFFSET(T1:T9812,ROW(T1:T9812)-ROW(INDEX(T1:T9812,1)),0)),--(T1:T9812="yes"))<strike></strike>

 

[MARKEGANDERSON]

Thanks Aladin. it is now returning a value of 525