Countif to visible cells only

[kokapelly]

Hi All,

So today i found out that i can't use

Please Login or Register  to view this content.

to show me how many times an acc# showes up if the excel sheet is auto filtered. I then tried

Please Login or Register  to view this content.

But it seams i can not refference a cell. any help?

[kokapelly]

aww man comeone now... someone knows

[Oldmaster]

Hey, not really sure how that second formula works to get you the result.

But, is this a complicated autofilter? Could you go about this indirectly by creating a second column that shows the value in Column F only if it meets the conditions of the autofilter? Then you can go ahead and count that column instead

[newdoverman]

What do you mean by "But it seams i can not refference a cell. any help?"

A workbook would help to understand what you are wanting to do.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[kokapelly]

Ok so attached a file Coulm I has the Countif formula that shows me how many time a policy shows up., but if you filter out on column D company name "Something" policy 333333333 will still show the count as 6 not 3 once the filter is in place.

[XOR LX]

Hi.

I certainly wouldn't use an upper range reference as high as 10,000 unless absolutely necessary, but this should work in I2 and copied down:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($F$2,ROW($F$2:$F$10100)-MIN(ROW($F$2:$F$10100)),,,)),0+($F$2:$F$10100=F2))

Regards

[bmouse]

COUNTIF is working properly, check the formula. The rest of the cells in column I and all other columns for that matter are just hidden, so at this point your 1st COUNTIF (now in row 2, column I) is actually the one from row 7 in the original list. The auto-filter does not change the formula it just hides any other cells that do not match the filter.

[kokapelly]

Thanks XOR and no the range ref is nessary as the data we have is that large....
Bmouse not sure what your saying sorry...

[bmouse]

I was just pointing out that COUNTIF is working as intended and auto-filters do not change formulas, nevermind.

[newdoverman]

Does this slight variation work for you?