I'm using SUMPRODUCT to total quaity of certain SKUS by month pulling data from the Order Items CSV Sheet. The total on the Totals sheet is only 1,167 but when I do a SUM of the QTY column in the Orders Item CSV is shows 1,411. I've also done a pivot table that came out with the same amount.

Here is where the data is stored: Order Items CSV

This is my Orders by Month and SUMPRODUCT Sheet: Orders Data Report

My formula is this:

=if(isblank($A2),0, SUMPRODUCT((MONTH('Order Items CSV'!$A$3:$A)=MONTH(DATEVALUE(B$1&"1")))('Order Items CSV'!$AG$3:$AG=$A2)('Order Items CSV'!$D$3:$D)))

I do not know why it is only totaling some of the SKUS. I've spot checked about ten SKUS through a pivot table and their Quantities are correct to what shows on the Order Data Report Sheet.

CodePudding user response：

Welcome to StackOverflow. Without access to the sheet it's quite difficult to diagnose. What I can think of is something with the headers and calculations of months. A variation considering the column in which the cell is placed is:

=if(isblank($A2),0, SUMPRODUCT((MONTH('Order Items CSV'!$A$3:$A)=(COLUMN()-1))('Order Items CSV'!$AG$3:$AG=$A2)('Order Items CSV'!$D$3:$D)))

Or, for the whole table (erasing all previous formulae, and putting this in B2):

=MAKEARRAY(COUNTA(A2:A),12,LAMBDA(r,c,SUM(FILTER('Order Items CSV'!D2:D,'Order Items CSV'!AG2:AG=INDEX(A2:A,r),INDEX(MONTH('Order Items CSV'!$A$2:$A))=c))))

And, another option with QUERY, try it in a blank sheet because it will also bring the names of products:

=QUERY('Order Items CSV'!A:AG,"Select AG,SUM(D) group by AG pivot by MONTH(A) 1",1)

CodePudding user response：

Sumproduct formula is quite powerfull formula but takes lots of times to process as it goes thorogh each cell everytime wasting lot of process time.

Better solution is Query as suggested by Martin.