LinkBack URL
About LinkBacks
Hi
How do I use a countif formula and ONLY have it apply to data AFTER The filter has been applied in a table?
I read up on it and only saw a subtotal solution.
Any help is appreciated.
Alex
Hi
How do I use a countif formula and ONLY have it apply to data AFTER The filter has been applied in a table?
I read up on it and only saw a subtotal solution.
Any help is appreciated.
Alex
Basically I want to count cells that are greater than 5 in column when I select an URL.
Countif.jpg
From looking at the screen shot I would think that the following formula might work:Formula:
Please Login or Register to view this content.
If not, however, I would suggest uploading a sample of the spreadsheet from which the screenshot was taken. Let us know how the filter will be applied and what your expected count is.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi
Thanks for reply! Sorry for delay, I missed the email alert!
I've attached the demo files.
Basically it looks up the url and counts how many are greater than TF 5!
Really appreciate the help. Was doing this manually and thought there might be a formula for it!
(Ignore table sorting part of this). We were manually filtering table and checking remaining entries, but it seems this could be done with a formula?
Hi,
Much as suggested already
=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$2,ROW(Table13[URL])-ROW($A$2),,1))*(Table13[URL]=E2)*(Table13[TrustFlow] > 5))
Don
Please remember to mark your thread 'Solved' when appropriate.
Not sure why you wouldn't put =IF([@TrustFlow]>5,1,0)in cell d3
then pivot the data, url as row, column1 as the sum,
That is awesome! You just saved me from much manual work!
Rep added!
I've got it all working but the formula seems very heavy. It's always taking forever to update the spreadsheet. (I have a few thousand rows and check for TF0>, TF10>, TF20>,TF30>,TF40>,TF50> so it adds up quickly!)
Is there a less heavy workaround?
You can add a separate column to the table with a SUBTOTAL formula in it and then use that instead of the volatile SUBTOTAL/OFFSET combination.
For example add a new column to the table, called VisCheck with the formula
=0+SUBTOTAL(103,[@URL])
then amend the totals formula to
=COUNTIFS(Table13[VisCheck],1,Table13[URL],F2,Table13[TrustFlow],">5")
where F2 is the URL cell to look for.
That would be much better.
1) Do I need a VisCheck for each test? (I.e. those with TF5+, TF 10+) or is it a general check?
2) What does this Vischeck column do?
Per the attachment it just returns 1 if a row is visible, or 0 if not. You only require one such column.
Thanks! Do you know if the following is possible?
I'm trying to detect if it has foreign anchors in a row and then report back on that?
Foreign = Non English (I.e. Russian, Chinese, Arabic, etc)
(i.e. does it contain anything not [^a-zA-z_-0-9])?)
I've attached Demo2 that has this extra check that I'm doing.
Is this possible at all?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks