Hi All,
So today i found out that i can't useto show me how many times an acc# showes up if the excel sheet is auto filtered. I then tried
=Countif(F:F,F2)But it seams i can not refference a cell. any help?=SUMPRODUCT(SUBTOTAL(3,OFFSET(F2,ROW(F2:F10100)-ROW(F2),0)),(F2:F10100="F2")+0)
aww man comeone now... someone knows
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
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
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
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.
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
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.
Thanks XOR and no the range ref is nessary as the data we have is that large....
Bmouse not sure what your saying sorry...
I was just pointing out that COUNTIF is working as intended and auto-filters do not change formulas, nevermind.
Does this slight variation work for you?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
kokapelly 
Register To Reply
Bookmarks