Google-sheets – Add cells based on the content of another column

formulasgoogle sheets

I have this sheet and I am trying to add the values of June but the issue that I have is that these values will update often with so I need to find a formula that accounts for that. The other issue is that these values will live on different rows, so one instance might be 'B1' (14) but then it might be 'B3' (2) etc.

I think something with =COUNTIF("June",…) might work, but that's where I'm stuck.

enter image description here

Best Answer

The solution, as I understand it, is as follows.

By setting a FILTER formula (array & condition) you create the first step, the "what are we looking for, specifically" then that formula gets wrapped in a SUM formula which adds the array values.

So, in the case above =SUM(FILTER(B:B,A:A="June")) this formula is looking for the values, all of them, in column B and then it filters the results if those values meet the criteria, in my case anything with June. Lastly, it adds those values. Here you can see the updated sheet with the formula in action.

Related Solutions

Google Sheets – Define Range in Function Using Cell Value

Use INDIRECT() to get a cell reference by a string, which in your case is coming from a cell. To use the range as defined in cell C2 but refer to a sheet called data, use:

=countif(INDIRECT("data!"&C2, TRUE),A2)

Hopefully I understand the final formula correctly. If not, replace the range reference with the INDIRECT() function. If this does not work, first check your formula with a manual entry of the range. If that works, there is a syntax issue with the INDIRECT() function so try putting

=INDIRECT(C2, TRUE)

in a cell and see what is returned to check that as well.

Google-sheets – How to create a formula in Google Sheets that can sum specific cells based on multiple criteria

Several Google Sheets built-in functions like SUM require that their arguments be numbers and if some of them are text in most of the cases they will return an error message. Exceptions are text values that could be coerced to numbers like numbers quote enclosed.

There are several built-in functions that could be used to handle unknown cell value types

IFERROR If the first argument returns an error, returns the second argument
ISNUMBER Returns true if the argument is a number value, false otherwise
ISTEXT Returns true if the argument is a text value, false otherwise

the question doesn't include the expected result when the cells have a text value.

Let say that the expected result is 0, then we could use the following formula:

=IFERROR(SUM(D2,H2,M2),0)

Best Answer

Related Solutions

Google Sheets – Define Range in Function Using Cell Value

Google-sheets – How to create a formula in Google Sheets that can sum specific cells based on multiple criteria

Related Topic