Google-sheets – Calculate monthly sum() on pivote table

google sheetsgoogle-forms

I have a Google Form. Upon submit following fields are populated:

  1. Timestamp
  2. Product Description
  3. Unit Price
  4. Quanitity

I have generated a pivot table from this data, in a Google Spreadsheet. The first column is a timestamp and the second column is the sum of Unit Price. All is grouped by the timestamp.

I don't want it. I want to show the year-month at column 1 and the total costs on that month. It should be group by that month.

In SQL I would run this query:

SELECT 
    DATE_FORMAT(`Timestamp`, '%Y-%b'), 
    SUM(`Unit Price`*`Quantity`) 
FROM 
    TABLE1
GROUP BY DATE_FORMAT(`Timestamp`, '%Y-%b')

Best Answer

You can use =text(number,format) to add a column to your original data set.

You can then use the pivot table to group them by year-month or you can use =sumif(range,criteria,sum_range) to calculate the sum based on the data in the new year-month column.

Related Topic