If the values are date or number in a column and sorted, then it is easy to search a value in that column and sum up to that row in another column in Google Sheets. For this, we can depend on several formulas.

The formulas include Sumif, Sumproduct, Sum+Filter, Sum+IF or Query. Let’s address this later.

Things will be different in the following scenarios.

• Duplicate values in the search column.
• Unsorted data in the column.

I’ll try to clear all of your doubts regarding searching a value in a column and sum up to that (found) row in another column in Google Sheets.

For the time being, let’s forget about the duplicates. Let me give you examples of how to sum a column, up to the search value found row.

Note: When you have doubts about any function mentioned in this tutorial, please check my Google Sheets Function Guide.

Search and Sum Based on a Unique Value Column in Google Sheets

Date or Number Value Column (Sorted Ascending)

In the following example, I want to search the date 05-Jan-2020 in column B then sum up to row # 6 (C2:C6) as it will be the ‘found’ row.

In this case, we can use any of the following formulas. In all the formulas I have included two conditions. They are;

• The dates in B2:B must be less than or equal to 05-Jan-2020.
• The cells in the range B2:B don’t be blank. If blank, omit the corresponding cells in C2:C.
  • If you want to include the blank row, simply remove the second criterion B2:B<>"" from all the formulas. In Query it is different. There remove and B is not null part.

1. Sum+Filter

=sum(filter(C2:C,B2:B<=date(2020,1,5),B2:B<>""))

2. Sum + IF

=sum(ArrayFormula(if((B2:B<=date(2020,1,5))*(B2:B<>"")=1,C2:C)))

3. Query

=query(B1:C,"Select Sum(C) where B<=Date'2020-1-5' and B is not null label Sum(C)''")

Normally the query formula includes a label with the output. To remove that, I have used the LABEL clause as detailed here – Understand the Label Clause in Google Sheets Query.

4. Sumproduct

=sumproduct((B2:B<=date(2020,1,5))*(B2:B<>"")=1,C2:C)

5. Sumif

=sumif(B2:B10,"<="&date(2020,1,5),C2:C10)

In all of the above 5 formulas, which search a date/number value in column B and sum column C up to that row, I have used the criterion as a direct reference.

To bring flexibility, I mean to easily change the criterion without editing the formula, we can use them as a cell reference. It varies based on functions.

Assume the criterion 05-Jan-2020 is in cell D1. Then the above formulas will be used as follows.

=sum(filter(C2:C,B2:B<=D1,B2:B<>""))
=sum(ArrayFormula(if((B2:B<=D1)*(B2:B<>"")=1,C2:C)))
=query(B1:C,"Select Sum(C) where B<=date'"&TEXT(D1,"yyyy-mm-dd")&"' and B is not null")
=sumproduct((B2:B<=D1)*(B2:B<>"")=1,C2:C)
=sumif(B2:B10,"<="&D1,C2:C10)

While going through the above search and sum up to that row formulas, you can understand that the Query is entirely different in using the criteria.

You must enter the criteria in a specific pattern and it varies based on criteria.

For date criteria use in Query, please refer to my guide – How to Use Date Criteria in Query Function in Google Sheets. For learning the proper use of all types of criteria in Query please check my following guide – Examples to the Use of Literals in Query in Google Sheets.

Date or Number Value Column (Sorted – Descending)

If the dates are sorted in column B in descending order, the criterion part of the above formulas needs to be changed.

The changes are minimal. Just change <= to >=. See the formulas after changes to search value in a column and sum up to that row from another column.

=sum(filter(C2:C,B2:B>=D1,B2:B<>""))
=sum(ArrayFormula(if((B2:B>=D1)*(B2:B<>"")=1,C2:C)))
=query(B1:C,"Select Sum(C) where B>=date'"&TEXT(D1,"yyyy-mm-dd")&"' and B is not null label sum(C)''")
=sumproduct((B2:B>=D1)*(B2:B<>"")=1,C2:C)
=sumif(B2:B10,">="&D1,C2:C10)

String Value Column

What about string values in column B?

All the above formulas except the Query will work in a string column too. The sorted/unsorted formula difference will apply here also.

Here is an example. I am not repeating how to use these formulas when values are sorted in descending order. Because the changes are in the use of <= and >= operators.

Search a Value and Sum up to That Row Irrespective of Sort order and Value Type

All the above search and sum up to that row formulas were tried in a sorted column and with specific data types like numbers, strings or dates.

I want a more acceptable formula to search value and sum up to that row because of the reasons cited below.

Search in a column contains;

• Mixed data type values.
• Alphanumeric values.
• Unsure whether the data is sorted or unsorted.

In such scenarios, we can’t use the earlier formulas. Here is an ‘all-weather’ formula to use which will work with sorted and unsorted data. Also with date, number, and string value columns.

All-Weather Search and Sum Formula

=sum(array_constrain(C2:C,match(D1,B2:B,0),1))

This formula requires an exact match (not case sensitive) of the search key in the search column. Because the data is not sorted.

Since the comparison operators are in use with the criterion, earlier formulas in a sorted range match the value less than or equal to the search key value.

Similarly, in a sorted range, we can tune the above ‘all-weather’ formula for the nearest match. How?

In the Match part of the formula, i.e. match(D1,B2:B,0), 0 represents the sort order (unsorted/exact match). Change it to 1 for data sorted in ascending order and for descending order use -1 instead.

To exclude blanks (if any cell in column B is blank) use the below version of the ‘all-weather’ formula.

=sum(array_constrain(filter(C2:C,B2:B<>""),match(D1,filter(B2:B,B2:B<>""),0),1))

The just above Match formula point is applicable here also.

In concise this formula is enough to search value and sum up to that row in Google Sheets. Applicable to sorted, unsorted range in a unique value column.

Search and Sum Based on a Duplicate Value Column in Google Sheets

Sum Up to the First Occurrence of Search Value

Here we can use the just above formula.

Sum Up to the Last Occurrence of Search Value

To search the last occurrence of value and sum up to that row in another column in Google Sheets, we can use our earlier formulas in a sorted range.

Avoid the Query and in the ‘all-weather’ formula use 1 or -1 within Match based on the data sort order. But none of the above formulas will work in an unsorted range.

Find the formulas below to search the last occurrence of a value in an unsorted range and sum up to that row.

Excluding Blank Row:

=Query(indirect("B2:"&ArrayFormula(cell("address",lookup(2,1/{B2:B=D1},C2:C)))),"Select Sum(C) where B is not null label Sum(C)''")

Including Blank Row:

=Query(indirect("B2:"&ArrayFormula(cell("address",lookup(2,1/{B2:B=D1},C2:C)))),"Select Sum(C) label Sum(C)''")

That’s all. Thanks for the stay, enjoy!