Good morning! It’s been a little bit since my last bug fix, but I came across another issue. It appears that the Savings
column C
is not respecting the Hide
flag for categories (and by proxy Available
column F
. This became apparent with my hidden Investment category that contains mostly unbalanced transfers. This category is otherwise hidden and values don’t contribute to Budget
or Actuals
. Removing the Savings
flag from the category removed it from the Savings
column, but that introduces a corresponding offset error.
I was able to address this by concatenating checks in each of the SUMIF()
functions for an empty string in Hide From Reports
column AF7:AF
. Here is my updated formula in C8
:
=ARRAYFORMULA(IF($L8:$L="Category",IF(ISBLANK(VLOOKUP($B8:$B,{$AC$7:$AC,$AO$7:$AO},2,FALSE)),if($BC$6="Savings",VLOOKUP($B8:$B,{$AC$7:$AC,$AJ$7:$AJ},2,FALSE),iferror(1/0)),VLOOKUP($B8:$B,{$AC$7:$AC,$AO$7:$AO},2,FALSE)+if($BC$6="Savings",VLOOKUP($B8:$B,{$AC$7:$AC,$AJ$7:$AJ},2,FALSE),0)),IF($L8:$L="Type",SUMIF($AD$7:$AD&$AF$7:$AF,$B8:$B&"",$AO$7:$AO)+if($BC$6="Savings",SUMIF($AD$7:$AD&$AF$7:$AF,$B8:$B&"",$AJ$7:$AJ),0),IF(ISBLANK($L8:$L),IFERROR(1/0),SUMIF($AD$7:$AD&$AE$7:$AE&$AF$7:$AF,$L8:$L&$B8:$B&"",$AO$7:$AO)+if($BC$6="Savings",SUMIF($AD$7:$AD&$AE$7:$AE&$AF$7:$AF,$L8:$L&$B8:$B&"",$AJ$7:$AJ),0)))))
After making the change I was able to confirm that removing the Hide
flag displays the categories as expected.