Level up your Google Sheet Fu

So recently I have been doing a heap of spreadsheet work, trying to do some better modelling of costs and getting better insights into data generally.

For my current task I needed to be able to read in a dynamic list of items (which change all the time), and SUM them up over a number of months in another sheet. The ultimate goal was to not have add a new products then update the aggregate sheet.

So to demonstrate this, here is the outcome I was after (obviously this is generalised for this post). Below is the “sheet1” which shows a list of products and their total sales over each month:

On “sheet2”, I wanted a dynamic list which generated a list of the products from “sheet1” then did a total of all months for each product, here is the output I was after:

Easy enough you say BUT how do I make it dynamic?! Here is the formula I ended up with, I will break to down to show you how it all works together.

The crux of this solution revolves around getting the list of products, we achieve that with the `QUERY` formula.

query('sheet1'!A:A,"select A where A = '"&A2&"'")

The way this works is, it goes to column A (represented by sheet1!A:A), “selects” a value from column where it matches “A2”, which in our case is product1. This may not make sense straight away but hang in there, this becomes important when we combine it with the MATCH formula.

Next we wrap the QUERY into a MATCH formula like so:

MATCH(query('sheet1'!A:A,"select A where A='"&A2&"'"),'sheet1'!A:A,0)

This is where the “where A=” becomes important, this enables us to go looking for (ie MATCH) “product1”, that is what the QUERY gives us, the string MATCH uses to match by. We then tell it where to go looking for that value ie “sheet1!A:A” , column A. The 0 at the end is just telling MATCH how to respond, take a deeper look into the documentation for this formula if you are interested but for our case this sets MATCH to return the ROW where the result was found.

Now comes the cool part, INDIRECT. I hadn’t used this before and really didn’t understand what it was for until I realised it allowed me to build CELL references and it will either return the value from that “created” cell address or it is used as a reference when using things like SUM.

So in essence, think of it as a way to concatenate results together to create a CELL reference:

INDIRECT("B"&<result from MATCH>)

Then the SUM part is easy you just use two INDIRECT statements to create a range:

INDIRECT("B"&<result from MATCH>):INDIRECT("D"&<result from MATCH>)

and then use SUM to get the total:

SUM(INDIRECT("B"&<result from MATCH>):INDIRECT("D"&<result from MATCH>))

You can then use this on each line of your dynamic list of products.

I found this super useful so hopefully people get some value from it.