Subtotal CountIf

[ChrisRitchie]

Hi all

Win7, excel 2010

Is there a countif subtotal forumula? I would like count the number of X's in a column, but the total will change after filtering the column.

Thanks
Chris

 

[sheetspread]

Well a text function on the formulas parses just the results. So try the label column:

=SUMPRODUCT(--(RIGHT(A2:A43,5)="Total"),--(RIGHT(A2:A43,11)<>"Grand
Total"))

To exclude the grand total,

=SUMPRODUCT(--(RIGHT(A2:A43,5)="Total"),--(LEFT(A2:A43,5)<>"Grand"))

 

[pgc01]

Hi Chris

With the header for the filter in A1, and the data in A2:A10, try:

=SUMPRODUCT(--(A2:A10="X"),SUBTOTAL(3,OFFSET(A2,ROW(A2:A10)-ROW(A2),0)))

 

[ChrisRitchie]

Great thanks all!

I really appreciate all the support

 

[pgc01]

I'm glad it helped. Thanks for the feedback.