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.