Are you tired of struggling to sum only the visible rows in Google Sheets? Are you frustrated by the limitations of the standard SUM function? Well, worry no more! In this article, I will teach you a simple trick to omit hidden or filtered-out values and sum only the visible rows using the powerful SUBTOTAL function in Google Sheets. Hold onto your hats, my friends, because I’m about to spill the beans on this juicy secret!
The Magic Formula
To achieve this wizardry, we will be using the SUBTOTAL function with a specific code. The SUBTOTAL function is like the cooler, older sibling of the SUM function. It can not only sum values but also perform eleven types of aggregation. To sum only the visible rows, we will use function code 9 (to omit filtered-out rows) or function code 109 (to omit both filtered-out and hidden rows). Let me show you how it’s done!
Formula to Omit Hidden or Filtered-Out Values in Sum
Let’s start with a basic example, shall we? Imagine you have a range of values in cells B2 to B5 that you want to sum, but you also want to exclude any hidden or filtered-out rows. Here’s what you need to do:
=SUBTOTAL(109,B2:B5)By using this formula, any values in hidden or filtered-out rows will be ignored, giving you the sum of only the visible rows. It’s like magic!
Source Data and SUBTOTAL Formula in Two Different Sheets
But wait, there’s more! What if your source data is in one sheet and you want to place the SUBTOTAL formula in another sheet within the same file? Fear not, my dear friends, for I have a solution for you. Simply follow these steps:
- If your range to sum is in Sheet1, use the following formula:
=SUBTOTAL(9,Sheet1!B2:B5) - You can use this formula in any sheet within the same file.
Omitting Hidden or Filtered-Out Values in IMPORTRANGE Data
Ah, the plot thickens! What if you want to import data from one Google Sheets file to another, but you still want to omit hidden or filtered-out values when summing the imported data? Have no fear, my friends, because I have an ingenious workaround for you!
Here’s what you need to do:
-
Use the IMPORTRANGE function to import the desired data from one file to another. For example:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1as2K3NyJ58WipNSm56rtE__-DD3F6_x_RAp2ElpHNJw/edit#gid=1892488864","Sheet1!B2:B5")Remember to replace the URL with the URL of the sheet containing the data you want to import.
-
Wrap the IMPORTRANGE formula with the SUM function to get the total of the imported values. For example:
=SUM(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1as2K3NyJ58WipNSm56rtE__-DD3F6_x_RAp2ElpHNJw/edit#gid=1892488864","Sheet1!B2:B5"))
But here comes the clever part! Since we can’t directly use the SUBTOTAL function with the imported data, we’ll use a workaround involving the MAP and SUMIF functions:
- In the source sheet, insert the following MAP formula in cell C2 (make sure C2:C5 is blank beforehand):
=MAP(B2:B5, LAMBDA(row, SUBTOTAL(103,row))) - Import the range B2:C5 instead of B2:B5. For example:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1as2K3NyJ58WipNSm56rtE__-DD3F6_x_RAp2ElpHNJw/edit#gid=1892488864","Sheet1!B2:C5") - Use the following SUMIF formula instead of SUM to sum only the visible rows:
=SUMIF(B1:B4,1,A1:A4)
And just like that, hidden or filtered-out values in the imported data are omitted from the sum! It’s like magic, but better!
Now that you’ve learned this fantastic trick, go forth and conquer your Google Sheets spreadsheets like a pro. Remember, Crawlan is always here to provide you with the latest tips, tricks, and secrets to enhance your productivity. Happy summing!
