Hi, I know basic excel so I will try to explain my query as clear as possible. (apologies)
I have this formula below that count the rows if column H are labeled as "Approved" and column D have inputs except the word "Form"
=COUNTIFS(H10:H4608,"Approved",D10:D4608,"<>Form")
But I want to use filter. If I use filter I want the hidden rows to be excluded on the total.
What formula do I need to use? any tricks or suggestion.. Thank you in advance.
Try:
Please Login or Register to view this content.
Hi PaulM,
Thank you for your reply and help, but it seems it includes the "Form" which are "Approved".
basically, If the word "Form" is inputted on the Column D, it should not be included on the count. Condition is, if Column D is not "Form" and Column H is "Approved" it will be counted. and when filtered, hidden rows should be excluded.
Sorry, I hope my sentence is clear. but thank you.
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 then scroll down to Manage Attachments to open the upload window.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Try:
=SUMPRODUCT(($H$10:$H$4608="Approved")*($D$10:$D$4608<>"Form")*(SUBTOTAL(103,OFFSET($A$10,ROW($H$10:$H$4608)-MIN(ROW($H$10:$H$4608)),0))))
But a sample sheet would put an end to the guessing game...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi, attached a sample work sheet.
my condition is, if I filter column B, I need to get the total "Approved" documents (Column H). But all "Form" (Column D) shall not be included on the tally.
Thanks guys
=SUMPRODUCT(($H$10:$H$4608="Approved")*($D$10:$D$4608<>"Form")*(SUBTOTAL(103,OFFSET($B$10,ROW($H$10:$H$4608)-MIN(ROW($H$10:$H$4608)),0))))
seems to do it.
Hi glenn, yes that works perfectly. Thank you.
I appreciate all the reply. awesome.
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks