If you’re looking to efficiently search for a value in a column and sum up to that row in another column in Google Sheets, you’ve come to the right place. In this article, we’ll explore various formulas that will make this process a breeze. Whether your data is sorted or includes duplicates, we’ve got you covered. So let’s dive in!
Date or Number Value Column (Sorted Ascending)
Let’s start with a scenario where the values in the column are either dates or numbers, and they are sorted in ascending order. In this example, we want to search for the date “05-Jan-2020” in column B and sum up to the corresponding row (#6 in this case).
To achieve this, we can use any of the following formulas:
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)''")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 these formulas, we have included two conditions: the dates in column B must be less than or equal to “05-Jan-2020,” and the corresponding cells in column C must not be blank. Feel free to adjust these conditions as per your needs.
To make the formulas more flexible, you can use cell references instead of direct values. For example, if the criterion “05-Jan-2020” is in cell D1, the formulas will be modified 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)As you can see, the Query formula requires a specific format for the criteria. If you need help with date criteria in Query, check out my guide – How to Use Date Criteria in Query Function in Google Sheets.
Date or Number Value Column (Sorted – Descending)
Now, let’s consider a scenario where the dates are sorted in descending order in column B. In this case, you need to modify the criterion part of the formulas.
Simply change “<=” to “>=” in the formulas. Here are the modified formulas:
=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
But what if the values in column B are strings? Don’t worry, most of the formulas mentioned above will work for string columns too, except for the Query formula. The sorted/unsorted distinction still applies here as well.
For instance, consider this example:
Search a Value and Sum up to That Row Irrespective of Sort order and Value Type
Now let’s address a more general scenario where the search column may contain mixed data types, alphanumeric values, and you’re unsure whether the data is sorted.
For such cases, we have an “all-weather” formula that works with both sorted and unsorted data, regardless of the value type (dates, numbers, or strings).
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. Since the data is unsorted, we can’t rely on the comparison operators used in the earlier formulas.
If you want to exclude blanks, use this version of the formula:
=sum(array_constrain(filter(C2:C,B2:B<>""),match(D1,filter(B2:B,B2:B<>""),0),1))Remember to adjust the match part of the formula based on the sort order of your data. Use 1 for ascending order and -1 for descending order.
And there you have it! These formulas will empower you to search for a value in a column and sum up to that row in another column in Google Sheets, regardless of the data type and sort order. Now you can work with confidence, knowing that your data analysis is accurate and efficient.
For further guidance on Google Sheets functions and more advanced techniques, check out Crawlan.com. Happy sheeting!
