Google Sheets – Aggregate Values Based on Categories and Subcategories

formulasgoogle sheetsgoogle-sheets-query

I am trying to create a document which automatically tracks spending based on two categories.

For example, I have a sheet formatted similar to the table below:

Type    |   Amount  |   Category  
Visa    |   $100    |   Hair  
Chase   |   $200    |   Face  
Visa    |   $50     |   Hair  
Master  |   $150    |   Face  
Visa    |   $25     |   Face

And my goal is to update a seperate table with spending broken down for both "Type" and "Category", like this:

        |   Total  |   Hair  |   Face   |   Mouth
Total   |   $525   |   $150  |   $375   |   $0   
Visa    |   $175   |   $150  |   $25    |   $0   
Chase   |   $200   |   $0    |   $200   |   $0   
Transfr |   $0     |   $0    |   $0     |   $0   
Master  |   $150   |   $0    |   $150   |   $0

Note: all categories in the above table are intended to be static with only their relevant values getting filled in. That's why "Mouth" and "Transfr" are part of the table, even though they have no transactions in that category.

I have attempted to use the "Query" formula but I am only able to sort based on one category receiving results like these:

        |   Sum
Visa    |   $175  
Chase   |   $200 
Transfr |   $0   
Master  |   $150

        |   Sum
Hair    |   $300  
Face    |   $225

I am not sure if this is possible on Google Sheets, but any help would be appreciated.

Best Answer

C11: =SUM(C3:C9)
C12: =IFERROR(SUM(QUERY($B$3:$D$9,"select C where B='"&$B12&"'")),0)
D11: =IFERROR(SUM(QUERY($B$3:$D$9,"select C where D='"&D$10&"'")),0)
D12: =IFERROR(SUM(QUERY($B$3:$D$9,"select C where B='"&$B12&"' and D='"&D$10&"'")),0)
F15: =IFERROR(SUM(QUERY($B$3:$D$9,"select C where B='"&$B15&"' and D='"&F$10&"'")),0)
etc.

demo spreadsheet

Related Solutions

Google Sheets – Group Events by Day, Count and Aggregate Amounts

The error message says that the same column cannot be selected both with and without aggregation. One solution is to move the count function to another column:

=query(A:B, "select toDate(A), count(B), sum(B) group by toDate(A)")

Another solution, which is also applicable when there is no column B, is to duplicate column A before querying: {A:A, A:B} is the array with three columns in which the column A appears twice. One can refer to these columns as Col1, Col2, Col3 in the query string. The following formula returns the desired table:

=query({A:A, A:B}, "select toDate(Col1), count(Col2), sum(Col3) where Col1 is not null group by toDate(Col1) label toDate(Col1) 'date'")

For readability, the same query string with linebreaks:

select toDate(Col1), count(Col2), sum(Col3) 
where Col1 is not null 
group by toDate(Col1) 
label toDate(Col1) 'date'

Google Sheets Query – Specific Values Based on Date Ranges

Grouping

In general, aggregation functions such as SUM or COUNT are used in the presence of GROUP BY: one specifies how to group the rows so that summation or counting is executed within each group. You don't have any GROUP BY. It would make sense to group by E, so that you count each client id once.

Date format

When dates are used in a query, they have to appear like date 'yyyy-MM-dd', for example

where A >= date '2017-05-01' and A <= date '2017-05-21'

Creating such a string from dynamic dates (e.g., 60 days ago, =TODAY()-60 involves conversion to text using text and an annoying amount of concatenation:

"... where A >= date '" & text(today()-90, "yyyy-MM-dd") & "' and ..."

If you go this route, it is advisable to form the query string in its own cell so you can see what it is. query can take the query string by cell reference.

Alternative: filter

It's much easier to use filter for the task you described. Here is the count of unique values in E2:E where A entry is between 30 and 90 days ago:

=countunique(iferror(filter(E2:E, A2:A >= today()-90, A2:A <= today()-30)))

The wrapper iferror is included to suppress "#N/A" that filter returns when there are no matches. (As pointed out by ocWavean).