COUNTIF/S and SUMIF/S and filtered tables.

Jwilks

New Member
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!

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jasonb75

Well-known Member
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
Excel Formula:
``=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.

Replies
2
Views
95
Replies
12
Views
284
Replies
8
Views
715
Replies
2
Views
52
Replies
0
Views
175

1,127,370
Messages
5,624,294
Members
416,018
Latest member
mirceaon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back