1

COUNT.IF built-in functions in Google Sheets do not update calculations after my script run.

The script (invoked by a custom push button) take values from an input mask (in another sheet), in order to insert a line in the summary sheet, which is fed by input mask data. In another sheet, where a series of COUNT.IF formulas should report the current situation, nothing happens. The only way to refresh data is manually copying formula from another cell.

Auxiliary Notes:

  1. main menu automatic calculations option is regularly set.
  2. I inserted in the script the re-copying of all COUNT.IF formulas and the FLUSH instruction, but never changed.
  3. The COUNT.IF formulas not updating are pointing to dates, which are converted into strings, since there are strings in the summary table to be counted. I am afraid this is crucial, because other COUNT.IF formulas not involving dates regularly update.

How can I get an automatic refresh of such COUNT.IF column or what am I getting wrong?

Update required by TheMaster: below the capture of the formula. (I am sorry for the "italian" but I think so is enough clear).

  • Formula in F3
  • "COUNT.IF" is in italian "CONTA.SE", while "SE" stands for "IF".
  • Dates are in the B column, and are pointed by the F column formulas in order to be converted into text and counted in the "colAppuntamenti" summary range.

screenshot of the formula

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Gabriele
  • 31
  • 4
  • Could you show your countif formula? – TheMaster Oct 13 '21 at 10:25
  • AFAIK, It should automatically update. I'm not sure why there's a issue. Could you also add a [mcve] of a script(with ``SpreadsheetApp.flush()``)? – TheMaster Oct 15 '21 at 12:09
  • I am afraid it would not be useful to post the script, since it works perfectly. The SpreadsheetApp.flush() statement is the last instruction of the script execution, however the COUNT.IF functions pointing to native text arguments automatically works, while the same COUNT .IF function based on TEXT(dates;"date format") needs to be copied again on itself to delivery an updated value. Truly a mistery, and I prooved and experienced it on different devices. Is there another GAS statement (or function) to get a refresh of dates nested into functions? – Gabriele Oct 15 '21 at 17:25
  • How did I solve the problem? Of course, by making authomatic conversion of the dates column into text, so to get immediate working of the COUNT.IF functions, but so doing I miss the possibility to set dates directly by the date pick-up proposed by Google Sheets. – Gabriele Oct 15 '21 at 17:30
  • This is more of a external solution rather than treating the cause: You can simply modify all formulas from the script. Flush it. Remodify all formulas to original. There is a related sample script here: https://stackoverflow.com/a/63280135 – TheMaster Oct 15 '21 at 17:31
  • Thanks a lot, TheMaster, but I have a concern, indeed a couple. 1) The formula I use is a built.in one, not a custom one; 2) the argument of the formula changes, because "colAppuntamenti" changes since there has been a new row inserted into the summary database ("colAppuntamenti" is the column over which the COUNT.IF makes its computations). – Gabriele Oct 16 '21 at 15:48
  • Is it absurd talking of "bug"? – Gabriele Oct 16 '21 at 15:50
  • 1. I'm aware that it's a inbuilt formula. 2. Now that I look away from your initial claim that it got something to do with dates, I see that your issue probably has to do with namedranges. Could you reproduce the error, if you use a normal range(say `A:A`) instead? – TheMaster Oct 16 '21 at 15:53
  • HALT!!!! To overcome the problem encountered in the famous formula I transformed the dates of column B into texts. As a consequence, I suppressed the tranformation of date to text in the COUNT.IF formula. Following the last advice of TheMaster, I reintroduce the dates in the B column and I made a test: miracously, everything in the formula started working. At this point, I presume that the original COUNT.IF remained unchanged because the argument of the primary TEXT formula remained unchanged. The deduction: in the formulas EVERY argument have to change to invoke change of the formula result. – Gabriele Oct 17 '21 at 09:00
  • For the record: =IF(B16>0;COUNT.IF(colAppuntamenti;B16);"") – Gabriele Oct 17 '21 at 09:11

0 Answers0