yogi_From Table of Date And Amount Compute Monthly Sum For Each Of Twelve Months

Google Spreadsheet   Post  #2270

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Oct-14-2017

question by: Daniel Smith 111

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/FPFu-QpWxds;context-place=forum/docs

Issues with 'SUMIFS' Function in Google Sheets
Hi

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:

MONTH YEAR  ||  TOTAL SELL PRICE  ||  TOTAL QUANTITY

eg September 2017, $4365, 5

and:

YEAR  ||  TOTAL SELL PRICE  ||  TOTAL QUANTITY

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. 

Here is a shareable version of the doc:

https://docs.google.com/spreadsheets/d/1of-3r7qppJWDSlLu0ED96DWFgI5Lst33p_Qp565nyvM/edit?usp=sharing

Thanks

Daniel