Exclude Cells with Formulas in a sum

[artmusicstudio2]

hello community,


after hours of web-search , trials and even chatgpt-not working-solution
i have to ask here:

i would like to (or must) sum up a column of 300 cells, where some cells are formulas.

i tried with many differnet syntaxes (here an example of nly 3 rows, where 1 has a formula in a cell):

=SUMME(WENN(ISTZAHL(AR12:AR14);AR12:AR14;0))

=SUMMEWENN(AR12:AR24;">0";AR12:AR24) + SUMMEWENN(AR12:AR24;"<0";AR12:AR24)

and different sumifs varations with "isfromula" and so on.

but all formulas do not ignore the formula in the cells and the result ist doubled !

can anybody help ?

thanx
stan

[ArviLaanemets]

I'd advice to redesign your source table(s)! So that in every column are only either entered data, or formulas (in every column will be only one kind of information). This will help you to avoid not only the current problem, but also a lot of future ones.

[p45cal]

try:

=SUM(FILTER(AR12:AR14;ISERROR(FORMULATEXT(AR12:AR14))))

or in German if I've got it right:

=SUMME(FILTER(AR12:AR14;ISTFEHLER(FORMELTEXT(AR12:AR14))))

edit: Ach, I just realised you're using Excel 2019... I'll think again.
edit2: You mention ISFORMULA so your version of Excel is at least 2021? If so the formula above should work.
Here's one with ISFORMULA:

=SUMME(NICHT(ISTFORMEL(AR12:AR14))*AR12:AR14)

=SUM(NOT(ISFORMULA(AR12:AR14))*AR12:AR14)

[Pecoflyer]

And of course posting a sample sheet would have helped ( no pics please). Thx