CODEWITHSUNDEEP
google-sheetsfiltersumgoogle-sheets-formulasumifs
NavTushar CHS
NavTushar CHS

Reputation: 11

Sum of cells containing a formula in google sheets

I want the sum of values in a column. However I wish to ignore cells which do not contain a formula. I tried

 =SUMIF(A1:A10, ISFORMULA(A1:A10))

But that did not work

Upvotes: 1

Views: 959

Answers (2)

kirkg13
kirkg13

Reputation: 3010

This way might work for you. I added a column with the following formula, which unfortunately also doesn't work as an arrayformula. But if you put this at the top of your column, beside the one you want to sum, and double click the "drag down" blue square on the bottom right corner of the cell, it will fill all the way down, provided you have no blank rows. Dragging down also works obviously.

=IFNA(FORMULATEXT(A1),0)

This creates a column of zeroes and text formulas, which you can use as a filter for your SUMIF formula, as follows: (assumes data column is A1:A30, helper column is B1:B30. SUMIF is in C1)

=SUMIF(B1:B30,"=0",A1:A30)

enter image description here

Let me know if this is useful, or not.

Upvotes: 1

player0
player0

Reputation: 1

if its a small range you can do:

=SUM(FILTER(A1:A10, {ISFORMULA(A1); ISFORMULA(A2); ISFORMULA(A3); 
                     ISFORMULA(A4); ISFORMULA(A5); ISFORMULA(A6); 
                     ISFORMULA(A7); ISFORMULA(A8); ISFORMULA(A9); ISFORMULA(A10)})

0

Upvotes: 1

Related Questions