1

I have a column formulated in excel that returns all the business days from date X to today and it is automatically filled in, and there is a column to its right in which a COUNTIF formula is applied based on this first column , but the cells are not filled automatically as it happened before, but you have to drag down to complete the formulas, and I want this to be automatic too.

The first column is fulfilled with the following formula: =WORKDAY(MIN(Data\[Fecha_Completa\]-1);SEQUENCE(NETWORKDAYS(MIN(Data\[Fecha_Completa\]-1);MAX(Data\[Fecha_Completa\]-1)))), and the second one (which doesn´t fulfill automatically): =IF(COUNTIFS(Data\[Fecha_Completa\];A2)=0;A2;""); in which it is explained that, if the date of the first column is found in the database (Data[fechacompleta]), I want excel to return me nothing, but if it isn´t found, I want excel to return me the same date. Then the problem is that the second column isn´t fulfilled automatically at the same time as the first does.

enter image description here

As you may appreciate in the first image, the formula exists for that value, but once more dates are included in the database (as you may see in the second picture when we come to February) formula stop working and obliges me to drag it down.

I have tried to applicate the formula to the whole column but that isn´t what we are looking for as the file would be heavier.

enter image description here

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
Diego Domínguez Blanco
  • 13
  • 3

1 Answers1

0

To base a formula on a dynamically-spilled array, use a # in your range references. Therefore using A2# instead of A2 will automatically spill the formula to the same size as the formula in A2.

=IF(COUNTIFS(Data\[Fecha_Completa\];A2#)=0;A2#;"")
Spencer Barnes
  • 2,809
  • 1
  • 7
  • 26
  • Good morning! Thanks for your help. I have tried your tip and the result is the one that I have showed you in the picture I have just attached (#SPILL), do you know how can I solve it? – Diego Domínguez Blanco Feb 16 '23 at 10:14
  • A `#SPILL` error happens when a formula is trying to automatically extend into other cells (like your first-column formula is doing) but there is other data or formulas in the range it is trying to extend into. check all cells below the cell with the error and delete anything they have in them – Spencer Barnes Feb 16 '23 at 10:15
  • The blue dashed outline shows the range it is trying to spill to, I can see in the picture that there is other data/formulas contained within that outline from your earlier attempt - it is this other data that is causing the problem – Spencer Barnes Feb 16 '23 at 10:17