SUM of multiple values/variables in Google Sheets using "IFERROR"

Question

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 | [email protected] | skutest001 | Item1Name | 1 | 12.49 | Wholesale
0002 | 2/22/2017 | TestFirst | TestLast | [email protected] | skutest001 | Item1Name | 5 | 62.45 | Retail
0002 | 2/22/2017 | TestFirst | TestLast | [email protected] | skutest002 | Item2Name | 10 | 124.9 | Retail
0003 | 3/31/2017 | TestFirst | TestLast | [email protected] | skutest001 | Item1Name | 5 | 62.45 | Retail
0003 | 3/31/2017 | TestFirst | TestLast | [email protected] | skutest002 | Item2Name | 10 | 124.9 | Retail
0004 | 3/31/2017 | TestFirst | TestLast | [email protected] | 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 .

Answer 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 ")