COUNTIF/S and SUMIF/S and filtered tables.

[Jwilks]

Hi there,



Im trying to use the SUMIF/S and COUNTIF/S formulas to sum and count figures from an excel table, but the figures don’t update with just the visible cells.



On the occupier demand sheet, I am trying to sum the total sqft of space within each designation (F26:K26) using formula =SUMIFS(Table1[Size],Table1[Size],">=5001",Table1[Size], "<=10000")



And count using: =COUNTIFS(Table1[Size],">=5001", Table1[Size],"<=10000")



Then on the summary sheet, I am trying to count the number of requirements each month using: =COUNTIFS(Table1[Date],">="&B7,Table1[Date],"<="&E7)



And sum the total sqft that month using: =SUM(IF(MONTH(Table1[Date])=2,Table1[Size],0))



*Table one being the occupier demand table.



However when I filter the table, nothing changes and I can’t figure out how the formula to change them to only account for visible cells.



Any help is greatly appreciated!

 

[jasonb75]

Most formulas are applied to all cells, not just the visible ones. The only things that work with filtered / hidden rows are SUBTOTAL and AGGREGATE, neither of which are very friendly when it comes to using criteria as required by SUMIFS, COUNTIFS, etc.

The easiest fix is to add an extra column using the SUBTOTAL function for that row, then to base your formulas on that column. For your table, I would suggest using

=SUBTOTAL(9,[@[Date]])

in another column, then use that as the date column in your sum and count formulas. Using this, any filtered rows will evaluate as Dec-31 1899 for formula purposes.