Saturday, October 14, 2017

yogi_From Table of Date And Amount Compute Sum By Month And Year For All 12 Months

Google Spreadsheet   Post  #2271

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Oct-14-2017
question by: Daniel Smith 111
Issues with 'SUMIFS' Function in Google Sheets

I am trying to reference a data range between sheets using a SUMIFS function and I am having some trouble. Any help would be greatly appreciated as I am a little out of my depth. 

So what I want to do is display a sheet that sums the sell price and the quantity sold via month and another that sums via years. I can do this in a pivot table but I am unable to access the Pivot Table in Google Data Studio. I also want to only sum data where the string "Work" is at the start.

The categories I would like are:


eg September 2017, $4365, 5



eg 2017, $65452, 102

So I have created a few formulas that all fail or do not do what I want they can be seen on the 'AUTO Sums by Month' sheet.

Such as:
=SUMIFS('Workshop Sales All'!I2:I, 'Workshop Sales All'!C2:C,">="A2,'Workshop Sales All'!C2:C,"<" A3,'Workshop Sales All'!F1, not(iferror(search("*Work*"; ))))

What I want is a sum of the data from the date range eg total quantity sold for September 2017, then total quantity sold for October, 2017. The final layout I would like can be seen on  the 'Total Workshops by Month' and 'Total Workshops by Year' sheets.

I would also like it to only sum data that has the string "work" or 'Work" at the start of the 'Product Code' Column in the 'Workshop Sales All' sheet.