CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Highland Ranger
Highland Ranger

Reputation: 15

Google Sheets - Add seperate columns and show sum on another sheet

Ok. Here's my problem. I have a sheet that is created by a Google Form. It is formated like this...

enter image description here

This is part of an INVENTORY CONTROL. This sheet is showing the INCOMING stock. What I want to do is ADD each column (that could go infinately) for example Col B SVHD-01-8OZCUP and display that number on G2 on the sheet below. I also have another sheet that is basically the same showing OUTGOING stock and the sum of its columns would go in H below.

Then I just Subtract H from G in each row to get the Current Inventory. All I need help on is getting the Check-In Totals and Check-Out Totals from the above sheet onto the below sheet. Thanks.

enter image description here

Upvotes: 0

Views: 288

Answers (1)

Broly
Broly

Reputation: 921

UPDATE 2:

Making the SEQUENCE part of the formula dynamic as per the discussion in comments:

=ArrayFormula(IFNA(VLOOKUP("sum "&A2:A, TRANSPOSE(QUERY({'Re-Order'!A1:DF}, "select "&TEXTJOIN(", ", 1, ("sum(Col"&SEQUENCE((COUNTA('Re-Order'!1:1)-1), 1, 2)&")"))&"", 1)), 2, 0)))

This will only work if you do not have empty columns in the sheet.

UPDATE:

Added this formula in D2 in your Test B sheet:

=ArrayFormula(IFNA(VLOOKUP("sum "&A2:A, TRANSPOSE(QUERY({'Re-Order'!A1:DF}, "select "&TEXTJOIN(", ", 1, ("sum(Col"&SEQUENCE(109, 1, 2)&")"))&"", 1)), 2, 0))) and it seems to be working. Let me know if you have any questions.

PREVIOUS ANSWER:

Based on the screenshots that you have shared (I have not looked at the sheet in the comments) one way to do it is to sum all columns individually in the form and then lookup the value against the product in the second sheet (screenshot 2 of your question)

You can use the following formula in G2 in your the sheet in screenshot 2 of your question:

=ArrayFormula(VLOOKUP("sum "&A2:A, TRANSPOSE(QUERY({inventory_control_sheet_nameA1:DF}, "select "&TEXTJOIN(", ", 1, ("sum(Col"&SEQUENCE(109, 1, 2)&")"))&"", 1)), 2, 0))

This assumes that the input has columns through DF.

Upvotes: 1

Related Questions