Search code examples
google-sheetsspreadsheet

In Google Sheet parse cell input with asterisks to include values in a SUMIFS equation

This equation works as expected to sum values from row 3 based on their relationship to a series of dates listed in row 1: =SUMIFS(I3:DV3, I$1:DV$1,">="&today()-86, I$1:DV$1,"<"&today())

I now have to prefix some of the row 3 input with asterisks '*', but as expected, this equation doesn't read those cells as numerical. For the purpose of this equation only, I need to strip the asterisks and read the numerical value(s).

Tried chatgpt (no help). I suppose I need to use SUBSTITUTE, but no idea how to incorporate it with this equation.

Solution

  • You may try:

    =sum(ifna(filter(--substitute(I3:DV3,"*",),isbetween(I1:DV1,today()-86,today(),,0))))