Counting filtered cells

[ollyhughes1982]

Hi,

Re my recent posts regarding my parkrun spreadsheet, I am wondering if anyone might be able to help, please. I want to count the cells in the source worksheet where I have completed that parkrun (i.e. has a date in the date field). before using the filter formula in the source worksheet, I used to manually input the list and the formula below used to work:

=IF('All Completed Runs'!B4="","",(COUNTIF('All Completed Runs - EM R (UK)'!B4:B2003,">0"))&" out of"&" "&COUNTIF('All Completed Runs - EM R (UK)'!B4:B2003,"<>"))

What would I now need to change this to? It's actually correct that I haven't done any out of that region, but it should read 0 out of 44, rather than 2000.

File link is here: parkrun Chess Example.xlsx

It may take a minute or so to open, due to the size.

Thanks again.

 

[jasonb75]

File can't be opened, too big for excel online and no option to download for desktop.

I think that this is correct but have not been able to test it.

=IF('All Completed Runs'!B4="","",SUM(SUBTOTAL(2,Offset('All Completed Runs - EM R (UK)'!B4,ROW(All Completed Runs - EM R (UK)'!B4:B2003)-ROW(All Completed Runs - EM R (UK)'!B4),0,1,1))*(All Completed Runs - EM R (UK)'!B4:B2003>0))&" out of"&" "&SUM(SUBTOTAL(2,Offset('All Completed Runs - EM R (UK)'!B4,ROW(All Completed Runs - EM R (UK)'!B4:B2003)-ROW(All Completed Runs - EM R (UK)'!B4),0,1,1))))

 

[Fluff]

Another option is to simply change the 2nd countif to look at col A rather then B.

 

[jasonb75]

Another option is to simply change the 2nd countif to look at col A rather then B.

But that's not going to work if the filter is changed.

 

[Fluff]

It's related to this thread Possible IF selection to populate a list & is referring to the filter function, rather than autofilters.
I only know that as I helped on the other thread.

 

[jasonb75]

From the screen capture I had assumed normal filter, but if what you say is true then subtotal is not going to work because nothing will be hidden.

If it is related to the filter formula question then surely COUNTIF(B2#,">0") and COUNT(B2#) where B2# refers to the spill range of the filter formula, should work in place of the current countifs functions?

 

[Fluff]

I think that the filter formula is in col A & is text with a date in col B, so the formula could be changed to

=IF('All Completed Runs'!B4="","",(COUNTIF('All Completed Runs - EM R (UK)'!B4:B2003,">0"))&" out of"&" "&ROWS('All Completed Runs - EM R (UK)'!A4#))

 

[ollyhughes1982]

Thanks, both. I will try as soon as I can get back the laptop

 

[ollyhughes1982]

I think that the filter formula is in col A & is text with a date in col B, so the formula could be changed to

=IF('All Completed Runs'!B4="","",(COUNTIF('All Completed Runs - EM R (UK)'!B4:B2003,">0"))&" out of"&" "&ROWS('All Completed Runs - EM R (UK)'!A4#))

This worked. Thanks again.

 

[Fluff]

Glad we could help & thanks for the feedback.