• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNTIFS and SUMIFS with filtered applied to data.

avcape

New Member
I've spend a lot of time reading up on SUBTOTAL and SUMPRODUCT etc that should do the job but I can’t get my head around it.
The data set I have is a bit more complex as it uses various ranges.

I need help to convert the formulas I have (COUNTIFS and SUMIFS) to something else that will work with filtered data.
See att screenshot with all the detail.screenshot.jpg
Thank you
 

p45cal

Well-Known Member
I could do the formulae for you, at a push - they'd not be straightforward.
In the attached I've another suggestion which doesn't involve a single worksheet formula.
At cell A21 there's a table, like yours, showing similar results.
Instead of filtering the other sheet, then going back to the first sheet to see the results, you can do filtering in the same sheet as the result table. There are several ways you can do it, but I've added Slicers (you could use dropdowns instead) for Product Name, Nr, Area and Date - you can filter on any of the columns in this way. You can pick options from the slicers using single clicks of the mouse, or in conjunction with the Shift key to select a contiguous bunch at once, or with the Ctrl key to select multiple non-contigous items, even a combination of both, and click on the little filter icon with a red x over it in the very top right corner of a slicer to stop all filtering in that slicer.
One thing I didn't manage easily is your Count (my Total Count) column, column K; I cheated in the other columns in those rows by just hiding the negative sign in the Day Count rows, but the row total in colummn K takes into account the real numbers in that row, including the hidden negative signs, so your results and mine don't agree just in that totals column for just the Day Count.

ps. I converted your changing random numbers in the All sheet to fixed random numbers.
If you change the data on the All sheet, you'll need to refresh the result table by right-clicking on it and choosing Refresh.

Is this a direction you might go in?
It took minutes to set up, the table will change its size automatically depending on the filters you apply, including understanding the different numbers of days in each month.
 

Attachments

avcape

New Member
Hi p45cal

Thank you for your reply.! Appreciated.
I like your suggestion. I do. Always good to get another perspective.
I did not even know there was something like a Slicer :-o

Your suggestion will make it easy for the end user to use this. Intuitive and graphical. Looks so cool.!!
I will definitely play around with it and apply it in a live/real sheet. Those have over 150 000+ rows.

This is not just some xls sheet. This sheet is part of a project I'm running with that's vital to me.
I feel like an idiot here :-| But then again I answer other's questions on other forums on Linux and some Python code.


Thank you.
 
Top