With Google Docs I'm missing MS Access less and less

Warning: This article gets technical and may cause your eyes to glaze over. If you don't know how to create a spreadsheet, save yourself and stop reading now - better yet, sign up for my "Intro to Spreadsheets" and "Creating Spreadsheets" classes on Sept 13, 20 & 27 in Extension Learn or if you are an NC Cooperative Extension employee register with LMS

Okay - to be perfectly honest, I don't haven't used Access in years. Most of what most people would use a database for, can be done with a simple spreadsheet.

But, today, Susanne from Watauga County asked how to handle a shirt sale where they had three different colors, six styles and six sizes - and she didn't want to have a spreadsheet with 72 columns. She figured there had to be a better way - and there is.

Google Docs to the rescue. 

Google Docs has the ArrayFormula that is designed for working with multi-dimensional arrays.  (Think of an array as list, a 2-dimensional array as a table with rows and columns, a 3-dimensional array as a large Rubik's cube, a 4-dimensional array as a row of Rubik's cubes and so on.) The ArrayFormula is VERY powerful and did exactly what I wanted to do.

I've shared the spreadsheet ArrayFunctions Shirt Sale with everyone so you can see the formulas used and make a copy to use and play with.  The real work is done in the "Summary" sheet in cells C3:H22.



I'd never done anything like this before, so how did I figure it out? Let me walk you through my thought process (I use that term loosely).

I started thinking that I'd use the "SumIf" and the "AND" function, so I started to search the Google Docs Help for "SumIf". When I typed "SumIf" in the search box, "sumif more forum" came up as one of the recommended search phrases, so I searched for that.  One of the first results was "ARRAYFORMULA" and the description looked like it might help, so I read that page.  It gave me some insight, and hinted that this might be the tool I was looking for, but didn't give me exactly how to use it with the SUMIF function. (The ArrayFormula page is a good page to read if you want to understand some of the more advanced features in Google Docs Spreadsheets.)

So, I searched for "ArrayFormula sumif". One of the first pages returned was "Google Docs Help - ArrayFormula problem" and this had an example of exactly what I was looking for.

ARRAYFORMULA(SUMIF(A2:A5&B2:B5;"Day"&"John";C2:C5))

Let me translate this formula for you -
It is going to sum all the cells in C2 through C5 for the rows where A2 through A5 contains "Day" and the rows B2 through B5 contains "John".
By putting the ARRAYFORMULA in front of the formula, you can then use the ampersand "&" character to add ranges and criteria.

Since Susanne had three arrays to consider, size, style and color, the formula I used is:
=ArrayFormula(SumIf(Sales!H5:H292&Sales!I5:I292&Sales!J5:J292,B3&A3&C2,Sales!K5:K292))

Translated, it is summing all the cells in K5:K292 where column H equals B3 and column I equals A3 and column J equals C2.

Note: I removed the "$" anchors that were there for copying the cell to make it more legible.


Google Docs Spreadsheet is awesome!  

If your eyes are glazed over, you were warned.

P.S. If you haven't used  the UNIQUE function, you definitely need to check it out!