google-sheetsformulasumifs

Google sheets sum with condition

I am attempting to sum a series of cells, most of which have a number value, but some have a literal such as 'X' or 'U or are blank, for example

4
U
2
2
X
2
4


3

I would like to sum the column, and treat 'X' and 'U' as zero and ignore blanks.

I have attempted several functions that chatgpt has suggested, all of which result in either #value or #error and one of them resulted in #ref

These are some of the functions I've been suggested by chatgpt:

=SUM(IF(ISNUMBER(V3:V263), V3:V263, IF(V3:V263="X", 0, IF(V3:V263="U", 0, VALUE(V3:V263)))))
=SUM(IF(V3:V263="X", 0, IF(V3:V263="U", 0, V3:V263)))
=SUMIF (V3:V263, ISNUMBER(V3:V363), V3:V263),

After pointing out the #value and #error result to chatgpt, it suggested I create a new column and include this function into each cell, and then sum on the new column =IF(ISNUMBER(V3), V3, IF(V3="X", 0, IF(V3="U", 0, VALUE(V3))))

But I am reluctant to include a new column. The sheet is large and unwieldy as it is. Any suggestions.

Solution

  • Suggestion

    If you are attempting to calculate the sum of several cells, where you need to specifically treat some cells that contain the non-numeric characters 'X' or 'U' as 0 and simply ignore blank cells, you may want to try this method using the MAP and REGEXMATCH functions:

    =SUM(MAP(V3:V263, LAMBDA(x, IFERROR(IF(REGEXMATCH(x, "U|X"), 0, x),x))))

    Demo

    enter image description here

    References