Jul 02 2021 02:08 PM
Is there a way to test for a value in a range of cells and then, if some of the cells are not empty, send the sum of the non-empty cells to another single cell? I have been experimenting with formulas to accomplish this but always end up with a "Spill."
Jul 02 2021 02:48 PM
Jul 03 2021 02:28 AM - edited Jul 03 2021 08:52 AM
Solution...sum of the non-empty cells to another single cell
in C1:
=SUMIF(A1:A8,"<>",A1:A8)
That would work even if empty cells contain something like: =""
Jul 03 2021 03:01 AM
As variant
=IF(ISNA(XMATCH(,A1:A20)), "no blank cells", SUM(A1:A20))
if you mean blank cells, not cells with empty string "".
Jul 03 2021 11:38 AM - edited Jul 03 2021 11:42 AM
This works too. Thank you.
Jul 03 2021 11:40 AM - edited Jul 03 2021 11:41 AM
Perfect! Does exactly what I want it to do. This works with the 'Average' function as well. Thank you.
Jul 03 2021 11:49 AM
Jul 04 2021 12:01 PM
Perhaps I misunderstood the question: to sum only if there are no blank cells in the range, if at least one blank cell then return something else.
Otherwise that's as @Detlef Lewin suggested.
Jul 04 2021 09:31 PM
If this works as you expect I probably misunderstood something as well => Use Detlef's one:
=SUM(A1:A8)
Same goes with Average
=AVERAGE(A1:A8)
Could you unmark the current Best response and mark the one from Detlef instead please?
Thanks & nice day...
Jul 03 2021 02:28 AM - edited Jul 03 2021 08:52 AM
Solution...sum of the non-empty cells to another single cell
in C1:
=SUMIF(A1:A8,"<>",A1:A8)
That would work even if empty cells contain something like: =""