Is there a way to use sumproduct (or something else) to subtract from inventory with a checkbox in Google Sheets?
I've been racking my brain for the past week trying to figure out a way to create a semi-automatic inventory of my store in Google Sheets.
Currently I have one table where orders come in (automatically placed in). Each order gets 3 cells: timestamp, order number and order info. Timestamp and order number is self explanatory, order info is a comma-separated cell with what was purchased (for example: shirt, shirt, pants, shorts, shirt, shoes would be an order of 3 shirts, 1 pair of pants, 1 pair of shorts and a pair of shoes ) In addition I've added a true/false checkbox in column D.
On a second sheet I have what I'm calling my inventory. In this sheet, I have in the left-most column (column A) the names of each item. Next to that are columns for amount ordered in each month (each uses this craaaazy formula due to the way the timestamp comes in =SUMPRODUCT(((INT((IFERROR(--MID('Inventory-Orders'!$A$2:$A,FIND("CREATED",UPPER('Inventory-Orders'!$A$2:$A))+LEN("CREATED"),10),0)-IFERROR(--RIGHT('Inventory-Orders'!$A$2:$A,5),0)))>=B$1)*(INT((IFERROR(--MID('Inventory-Orders'!$A$2:$A,FIND("CREATED",UPPER('Inventory-Orders'!$A$2:$A))+LEN("CREATED"),10),0)-IFERROR(--RIGHT('Inventory-Orders'!$A$2:$A,5),0)))<=EOMONTH(B$1,0)))*((LEN('Inventory-Orders'!$C$2:$C)-LEN(SUBSTITUTE((UPPER('Inventory-Orders'!$C$2:$C)),UPPER($A2),"")))/LEN($A2)))
),
followed by a column of the total amount ordered (just a sum of the previous month columns) and one last column that is my remaining inventory.
Currently, I've been counting at the end of the week what I have left and updating it manually. I've been trying to figure out a way to combine the checkbox with that formula above to create an inventory that automatically subtracts the correct amount whenever I check the box in sheet 1 - Orders that marks it as fulfilled.
Nothing I seem to try works, I thought it would be a simple task, and it maybe that I'm overlooking something, so...is there a correct way to do this? I thought I could use the same function above with an IF statement but that doesn't get me anywhere good.
If you want to only count the cells which have the checkbox next to it checked, you could add the IF condition for each element in SUMPRODUCT. Something like this:
IF('Labels-Standard Orders'!$D$2:$D, ((INT(...))))
For example, from your sample, in C5 (June shorts) you could have this formula:
=SUMPRODUCT(IF('Labels-Standard Orders'!$D$2:$D,((INT((IFERROR(--MID('Labels-Standard Orders'!$A$2:$A,FIND("CREATED",UPPER('Labels-Standard Orders'!$A$2:$A))+LEN("CREATED"),10),0)-IFERROR(--RIGHT('Labels-Standard Orders'!$A$2:$A,5),0)))>=C$1)*(INT((IFERROR(--MID('Labels-Standard Orders'!$A$2:$A,FIND("CREATED",UPPER('Labels-Standard Orders'!$A$2:$A))+LEN("CREATED"),10),0)-IFERROR(--RIGHT('Labels-Standard Orders'!$A$2:$A,5),0)))<=EOMONTH(C$1,0))))*((LEN('Labels-Standard Orders'!$C$2:$C)-LEN(SUBSTITUTE((UPPER('Labels-Standard Orders'!$C$2:$C)),UPPER($A5),"")))/LEN($A5)))