1

I'm currently using the below formula to show the percentage of cells that show Yes in a column. Now I've got a fair bit of data in the spreadsheet. I've started filtering and have realised that this formula also includes hidden cells.

Is there a way I can edit the formula below to exclude any hidden rows?

=COUNTIF(H2:H9998,"Yes")/COUNTA(H2:H9998)
Improve this question
2
  • see: stackoverflow.com/questions/30718873/…
    – Scott Craner
    Feb 6, 2019 at 15:53
  • 1
    I've seen a few threads like that but I'm shocking at nesting formulas and with that particular example I can't really work out what I need to replace in that to get what I want, let alone if I need the same amount of brackets. Also it kind of explains how to do the countifs as a sumproduct / subtotal but then where does my counta fit into that?
    – Jim
    Feb 7, 2019 at 14:53

2 Answers 2

Reset to default
1

Figured this out in the end. Pieced something together from the Excel forum below and then just used the subtotal function again at the end and it seems to be working alright.

Formula I ended up using was...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(H2:H9998,ROW(H2:H9998)-ROW(H2),0,1)),--(H2:H9998="Yes"))/SUBTOTAL(103,H2:H9998)

The two resources that I managed to piece that together from were...

Excel Forum

Microsoft SUBTOTAL help page

No idea how it actually works and still don't understand it at all but it's working and that's all I need at the minute.

Improve this answer
1

SUBTOTAL works like this - SUBTOTAL(function_num,Range1,range2,...) where function_num is a number that telling what you want subtotal to do

For function_num, use single digit code 1-11 to includes manually hidden values, and use three digits code 101-111 to ignore manually hidden values, whereas filtered out values are always ignored.

1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

And at least one range should be there.

So, put a Filter on the column of "Yes", so that only "Yes" cells are visible, rest get filtered out.

As you want only the first value, the Yes value, to ignore hidden values, you need use subtotal only in the numerator. In the denominator, you want to count all values, including what is filtered out, so there subtotal is not required, a normal count will do the work.

Enter his formula:

=Subtotal(2,H2:H9998)/Count(H2:H9998)

As your actual data is not here, I can't try myself. But please try and if you face any difficulty, please make a comment here, I will look and reply.

Improve this answer
3
  • 1
    Yes I've googled and see that I will need to use subtotal somewhere in the formula but can't work out exactly how to formulate it to get it to do what I want it to.
    – Jim
    Feb 7, 2019 at 14:55
  • 1
    Edited my answer above to elaborate further. Please have a look and comment further how it comes up.
    – VSRawat
    Feb 7, 2019 at 16:03
  • 1
    Putting that in gave a DIV/0 error. Have managed to sort it now anyway, thanks for trying to help.
    – Jim
    Feb 7, 2019 at 16:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .