I’m trying to figure out how to add up numbers in a spreadsheet column while skipping over cells that contain formulas. My column has groups of numbers with subtotals at the top of each group. I want to get the grand total of all the individual numbers without including the subtotals.
I thought this formula would work:
=SUM(FILTER(A:A, NOT(ISFORMULA(A:A))))
But it’s not giving me the right result. I think I’m missing something about how ISFORMULA works with cells that show the result of a formula rather than the formula itself.
Does anyone know how to filter out cells with dynamic values or formulas when adding up a column? I’ve tried a few different approaches but can’t seem to get it right. Any help would be great!
I’ve dealt with this exact problem in my work, and I found a solution that might help you out. Instead of using ISFORMULA, try using the TYPE function. It returns 1 for numbers and 2 for text, which covers most manual entries. Here’s a formula that worked for me:
=SUM(FILTER(A:A, TYPE(A:A)=1))
This should sum up only the numeric values that were manually entered, skipping over formulas and their results. If you’re using an older version of Excel that doesn’t have FILTER, you can use an array formula like this:
=SUM(IF(TYPE(A:A)=1, A:A, 0))
Remember to enter it with Ctrl+Shift+Enter. This approach has been reliable for me across different spreadsheet versions and setups. Give it a shot and see if it solves your issue!
hey, have you tried using array formulas? something like this might work:
=SUM(IF(ISNUMBER(A:A), A:A, 0))
it should only add up actual numbers, skipping over anything else. make sure to enter it as an array formula (ctrl+shift+enter). let me know if that helps!
I’ve encountered a similar issue before, and I found that using SUBTOTAL can be quite effective in this scenario. Try this formula:
=SUBTOTAL(9, A:A)
The SUBTOTAL function with argument 9 calculates the sum while ignoring other SUBTOTALs in the range. This should give you the grand total of individual numbers without including the subtotals.
If that doesn’t work, you might need to combine it with an array formula to exclude specific rows. Something like:
=SUM(IF(ROW(A:A)<>ROW(SubtotalRows), A:A))
Replace ‘SubtotalRows’ with the actual row numbers of your subtotals. Remember to enter this as an array formula with Ctrl+Shift+Enter.