Home > database >  SUMIFS not working in table (is working outside of table format)
SUMIFS not working in table (is working outside of table format)

Time:11-02

We have the below formula inside column D of our table

=SUMIFS(Table1[@[10/10/2022]:[30/11/2026]],Table1[[#Headers],[10/10/2022]:[30/11/2026]],">="&TODAY())

Using SUMIFS as we may add other parameters in the future. Basically we are trying to sum entire rows but only from today in to the future. Historical data entered in these rows would not form the sum total as this would be catered for in the Actual column.

When we built this in a non table format it worked completely fine but now it is not working.

Fore reference, some of the date columns will be filled with numbers to sum and others not. Basically it's hour allocations dependant on the tasks being performed.

enter image description here

I'm hoping there is something simple we are missing to get this to work but haven't been able to find anything online. Can someone please point us in the right direction? Thanks very much

CodePudding user response:

The dates in the headers will be interpreted as text unless coerced otherwise, so try:

=SUMPRODUCT(Table1[@[10/10/2022]:[30/11/2026]],N(0 (Table1[[#Headers],[10/10/2022]:[30/11/2026]])>=TODAY()))

CodePudding user response:

=SUM((Table1[@[10/10/2022]:[30/11/2026]])*(--((Table1[[#Headers], [10/10/2022]:[30/11/2026]]) 0)>=TODAY()))
  • Related