Help with counting the only visible cells after filtering

Hi,

i have formula to count when date is due in 30 days (column D) and Selected has to be in Column E to Count. Like below

=COUNTIFS(D:D,">="&TODAY(),D:D,"<="&TODAY()+30,E:E,"Selected")

but this doesn't work when i filter the data it just keeps the count for the whole sheet. i would like it to just show the count for the visible cells after filtering the data

thanks in advance

shane

Add a helper column, say column F
In F2 and filled down put
=SUBTOTAL(103,E2)

Then use
=COUNTIFS(D:D,">="&TODAY(),D:D,"<="&TODAY()+30,E:E,"Selected",F:F,1)

Originally Posted by Jonmo1

Add a helper column, say column F
In F2 and filled down put
=SUBTOTAL(103,E2)

Then use
=COUNTIFS(D:D,">="&TODAY(),D:D,"<="&TODAY()+30,E:E,"Selected",F:F,1)

thanks it works well. could it not be done with sum of product? just wonder as i import the data from CSV then filter it so i would have to add the helper column every time? much appreciated the help

Another option is to filter in pivot table and count from there.

Originally Posted by Estevaoba

Another option is to filter in pivot table and count from there.

true but people i have to send the spreadsheet to are not going to understand how to do that they just want a visual count

If you insist on not using a helper column, you can give this a try:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(D2,ROW(D2:D100)-ROW(D2),0)),(D2:D100>=TODAY())*(D2:D100<=TODAY()+30)*(E2:E100="Selected"))

Originally Posted by Jonmo1

You're already adding 1 column with the countif right? What's one more..

Yes it can be done without the helper column, though it's not my specialty.
The subtotal can be combined within sumproduct somehow.

However, I usually advise against doing this.
Helper columns are NOT a bad thing. That's why they're called
'Help'er

As soon as you have 1 formula that you want to ignore hidden rows, it's likely you will have another, and another...
If you combine the subtotal work in the formula itself, then that work must be repeated for each formula that needs to ignore hidden rows.
That becomes quite inefficient.
Using the helper column, the work of the subtotal is done only once. And subsequent formulas that want to ignore hidden rows simply refer to that column.

I don't add any to countif columns I use another program when I press selected then I export to Csv but the program exports all the data even the non selected ones so import in excel sort buy 30days till due and selected (when I select in the other program import in excel it say selected in the column) I then use countif to see how many there is.

Thanks for the input though much appreciated

Ok, you're not adding a whole column. But you are adding at least a formula. I don't think it's too much to add a column of formulas.
Just opinion.

And looks like FalconDude gave you a single formula solution.

Originally Posted by 63falcondude

If you insist on not using a helper column, you can give this a try:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(D2,ROW(D2:D100)-ROW(D2),0)),(D2:D100>=TODAY())*(D2:D100<=TODAY()+30)*(E2:E100="Selected"))

thanks 63falcondude

done it again legend! now that formula is well complicated

thanks Jonmo1
much appreciate your input definitely worth keeping in mind the Helper Column

You're welcome. Glad we could help.

Originally Posted by sspreyer

now that formula is well complicated

Yes, it is complicated. It is also volatile meaning that it will recalculate every time that anything is done in the worksheet.

Sometimes a helper column is the better choice but that is, of course, up to you.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.