# 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.
Thank you

#### p45cal

##### Well-Known Member
Please supply a workbook, pictures are no use to anyone.

#### avcape

##### New Member
Will do. Thank you.
See attached xlsx sheet.

#### Attachments

• 73.5 KB Views: 5

#### 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

• 98.9 KB Views: 4

Hi p45cal