COUNTIF/S and SUMIF/S and filtered tables.

Jwilks

New Member
Joined
Jan 8, 2021
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
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!
Screenshot 2021-01-08 at 13.41.47.png
Screenshot 2021-01-08 at 13.41.56.png
Screenshot 2021-01-08 at 13.42.11.png
Screenshot 2021-01-08 at 13.42.16.png
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top