0

I have a Google Sheets (titled: ALLORDERS) that lists orders from my online store. These are listed as one single product sale per line. The output looks like:

Invoice Number | Invoice Date | Buyer Name FIRST | Buyer Name LAST | Buyer Email | SKU | Order Item Title | Quantity Shipped | Total Amount | Sales Channel
0001 | 1/31/2017 | TestFirst | TestLast | test@gmail.com | skutest001 | Item1Name | 1 | 12.49 | Wholesale
0002 | 2/22/2017 | TestFirst | TestLast | test@gmail.com | skutest001 | Item1Name | 5 | 62.45 | Retail
0002 | 2/22/2017 | TestFirst | TestLast | test@gmail.com | skutest002 | Item2Name | 10 | 124.9 | Retail
0003 | 3/31/2017 | TestFirst | TestLast | test@gmail.com | skutest001 | Item1Name | 5 | 62.45 | Retail
0003 | 3/31/2017 | TestFirst | TestLast | test@gmail.com | skutest002 | Item2Name | 10 | 124.9 | Retail
0004 | 3/31/2017 | TestFirst | TestLast | test@gmail.com | skutest001 | Item1Name | 5 | 62.45 | Wholesale

I'm looking to compile the information from this sheet into two other sheets (titled: Wholesale and Retail).

Here's an example of the desired output for: Wholesale (NOTE: Let me start by saying that the first four columns of this table [UPC, Artist, SKU, and Title] are imported from another sheet. These are fixed values that follow all of my products from system to system)

UPC | Artist | SKU | Title | 1 | Jan $ | 2 | Feb $ | 3 | Mar $ | 4 | Apr $ | 5 | May $ | 6 | Jun $ | 7 | Jul $ | 8 | Aug $ | 9 | Sep $ | 10 | Oct $ | 11 | Nov $ | 12 | Dec $ |  | TOTAL | TOTAL $
00123123123123 | Artist1 | skutest001 | Item1Name | 1 | 12.49 | 5 | 62.45 | 10 | 124.9 | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | 13 | 237.09 | #N/A | #N/A | #N/A | #N/A |  | #N/A | 0
00123123123124 | Artist1 | skutest002 | Item2Name | #N/A | #N/A | 10 | 124.9 | 20 | 249.8 | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | 19 | 312.01 | #N/A | #N/A | #N/A | #N/A |  | 0 | $0.00
00123123123125 | Artist2 | skutest003 | Item4Name |  | #N/A | #N/A |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0 | $0.00
00123123123126 | Artist3 | skutest004 | Item5Name |  | #N/A | #N/A |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0 | $0.00

Here's a little explanation of the fields and work so far:

  • The column headers of "1", "2", "3", etc. correspond with the MONTH() of the "Invoice Date" in the "ALLORDERS" sheet

  • The #N/A are results of their being no value for the formula to compute. In these cases, I've been attempting to get cells to populate with "0" instead.

  • I've been attempting to total the "Quantity shipped" per month, per SKU, per wholesale/retail For example, this is what I've cobbled together for the first SKU ("skutest001") under the column "1" (January):

    =ArrayFormula(sum(filter(IFERROR(VALUE(ALLORDERS!$H$2:$H)), regexmatch(ALLORDERS!$F$2:$F, $C2), MONTH(ALLORDERS!$B$2:$B)=$F1), ALLORDERS!$J$2=$E1))

However, I'm unable to get the "#N/A" values to read out as "0" - therefore, I'm unable to get "TOTAL" and "TOTAL $" columns to successfully sum. (I believe this is because I am running into problems with "IFERROR" having too many values.)

As I am new to Google Sheets and creating this type of thing, the more I look at this, the more confused I become. I appreciate any and all help that comes my way.

Here's an example sheet that I've shared to help provide a better example: https://docs.google.com/spreadsheets/d/1-uoG13vfYmcfff6rCqDy2fHkYguu87rvVvaghq653Vk/edit?usp=sharing .

1

The whole sheet is confusing. And your question lacks clarity. I think you want Wholesale!AE2. If so,

=sum(QUERY(transpose(F2:AC2),"select * skipping 2"))

AF2:

=sum(QUERY(transpose(G2:AC2),"select * skipping 2"))

Also this might be helpful:

=QUERY(ALLORDERS!A1:J7,"Select F, G, sum(H), sum(I) Where J='Wholesale' group by F, G pivot month(B)+1 ")
  • 1
    Thank you for the clarity. The sheet is probably very confusing because I've turned myself around trying to get this in place (I guess the sheet accurately reflects my confusion). Even so, you accurately diagnosed the ultimate problem - which was getting the lines to total even when the #N/A results were in the rows. Thank you! – dixonbrand Nov 12 '17 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.