0

I have a inventory spreadsheet that I am using to keep track of my inventory and I want to add a new location to track. I added the location into my forms and they populated on the spreadsheet fine. One of my tabs in my workbook is the quantities and this is where I want to keep track of what place has what equipment. I am trying to apply a formula that will work to look at the Form Responses Sheet and Pick out the Item, The amount and what location and either add or subtract from my total. I have 4 columns in it that Specify Location1 Add, Location 2 add, Location 1 subtract and location 2 subtract. The problem is I am receiving error codes that my Arrays dont match when they do, and then I tried another way and I am getting a a Argument value must be a range error.

Here are my two forumlas I am trying to use.

=ARRAYFORMULA( IF( LEN(A$2:A), SUMIF( 'Form Responses 1'!I2:I, A$2:A&'Form Responses 1'!X$2:X, "MVP"&'Form Responses 1'!J$2:J), ""))

This give the Argument value must be a range error

=ARRAYFORMULA( IF( LEN(A$2:A), SUMIFS( 'Form Responses 1'!N2:N, A$2:A, 'Form Responses 1'!Z2:Z, "SWA",'Form Responses 1'!O$2:O), ""))

This give the error that Array arguments to SUMIFS are of different size.

I do not see how they are different sizes though! I also think this would be the correct formula, The one above was a formula I tried after trying to fine a way to make it work.

Here is a copy of my spreadsheet and it is the Quantity tab that I am trying to figure out the formulas.

https://docs.google.com/spreadsheets/d/1hsv1_z6i7VPNj6WcP_xRpQyrlgpXJq7L-V3HhiPZBN4/edit?usp=sharing

1
  • Just taking a glance, there are problems throughout this sheet. For starters, you have three columns (X, Y, Z) in your 'Form Responses 1' sheet which all have the same header. This is never a good thing, as most forms rely on header copy in order to know where to assign field values. Columns Y and Z both have data incoming, while Column X is blank; yet your formula in question references the blank Column X. As to the arrays being of different sizes, you are trying to pair A2:A from your Quantity sheet of 12K+ rows with information from your 'Form Responses 1' Sheet of <500 rows.
    – Erik Tyler
    Jul 9 at 19:40

Your Answer

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

Browse other questions tagged or ask your own question.