CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
dudewad
dudewad

Reputation: 13933

Google Sheets: SUM with FILTER

This feels like it should be super simple, and I have a feeling I'm just missing some finer syntax points.

I have a column that contains groups of numbers. These groups have a leading cell that has a simple SUM formula for the following cells. The group length and number of groups is completely arbitrary.

I want to add up everything in the column that is not a formula - this is essentially a column of items and subtotals, and I want to then have a master total.

I've tried many, many things, but here is a simple attempt to hopefully illustrate what I'm trying to do. The issue probably lies with me misunderstanding how to use ISFORMULA in an iterator type formula function.

=SUM(FILTER(B:B, NOT(ISFORMULA(B:B))))

So essentially I'm looking for this to mean, filter column B where the criteria is any cell that is NOT a formula, and add all the remaining items up.

I'm sure this is doable... can't find anything on it.

Thanks.

EDIT

I realized that the above example works with ISNUMBER - so the syntax is correct. However, ISNUMBER doesn't work for my purposes since some of the header cells are formulas that evaluate to numbers. Unfortunately, ISFORMULA doesn't seem to evaluate to true when the cell contains a dynamic value, so perhaps my quesiton needs to be "How do I filter out cells that have dynamic values in them"?

Upvotes: 0

Views: 2974

Answers (3)

Keyrad
Keyrad

Reputation: 590

Just use the subtotal function to do the job. This function considers all your filters. =Subtotal(9, [your range]).
The first parameter is the function code for Sum function, you can find the list all function codes here.

BTW, the corresponding function in Microsoft Excel is Aggregate function.

Upvotes: 0

z..
z..

Reputation: 13013

The ISFORMULA function cannot evaluate arrays. You can use MAP as a workaround.

=SUM(FILTER(B:B, MAP(A:A,LAMBDA(a,NOT(ISFORMULA(a))))))

Upvotes: 0

Michael Cropper
Michael Cropper

Reputation: 1032

With the correct structure in your Spreadsheet the basic SUMIF or SUMIFS should work just like it does in Excel;

SUMIF(range, criterion, [sum_range])

Source: https://support.google.com/docs/answer/3093583?hl=en-GB

If you can't get that to work, the structure of your Spreadsheet is wrong. Spreadsheets work best when your structure them well in the first place rather than trying to do quirky things with poorly structured data, which rarely works well.

Upvotes: 1

Related Questions