If you’ve spent enough time trying to do Excel things in Google Docs, you might have run into this challenge: no SUMIFS.

An obscure Excel function, SUMIFS allows you to add together cells based on several sets of criteria. One problem: It isn’t recognized by Google Docs.

Instead, you can combine two functions: SUM and FILTER.

If you haven’t used it before, FILTER is a function that allows you to select only cells that meet certain criteria. The syntax is =FILTER(range, criteria1, criteria2, criteria3, etc).

For example, if column A has project names, column B has dates, and column C has project statuses, and I want to select all the names of projects completed before today, I might write =FILTER(A:A, B:B<TODAY(), C:C=”Complete”).

Now, what if instead of just selecting the names of projects, what I want is the total expenses (column D) associated with projects completed before today? I “nest” the FILTER function into a SUM function.

=SUM(FILTER(D:D,B:B<TODAY(), C:C=”Complete”).

And that’s how you get around Google Docs’ lack of SUMIFS!

Note: Google Sheets is replacing Google Spreadsheets and has SUMIFS. New documents created in Sheets can use SUMIFS, old Google Docs should use this.

More tips for Google Docs? Share in the comments!


Shared from neworganizing.com