Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup

How would I be able to SUM different columns and multiply by amounts based on criteria?

I am having a problem with a formula on Google Sheets. I have a spreadsheet that categorises different materials based on Tier.

enter image description here

All the information for this sheet is coming from a different tab in the same sheet which looks like this:

enter image description here

What I want the first sheet to do is, for example in the second picture on "SHOES_CLOTH_SET1" it states that it need 8 CLOTH (D and E200) which is perfect and easy to figure out, BUT, I need it to multiply the CLOTH amount by STK in I200. Also, as you can see in TIER in H199 it is T4, so, I need the SUM of this equation to be put into T4 under CLOTH in the first picture. So simply 8 * 40 IF TIER is equal to N48 in the first picture.

This is all fairly simple, BUT (again) The first sheet needs to look through multiple rows of information and multiply them individually so that the multiplications do not start to stack.

Not only that, my problem is that, as you can see in F227 in the second picture, CLOTH can also occur on the second row of materials in the dataset.

Soooo, the formula needs to look through 1 row, look for amount of MATERIAL and multiply by the number in STK INDIVIDUALLY and then look through the other column and see if CLOTH appears again.

And then place it into the spreadsheet in the first picture based on material and tier.

Please tell me if this is to badly explained, I would be more than happy to repeat the question differently.

Solution

  • try:

    =ARRAYFORMULA(IFNA(VLOOKUP(A2:A; QUERY(QUERY(QUERY(
 {Data!H2:H\ Data!D2:E\ Data!I2:I; Data!H2:H\ Data!F2:G\ Data!I2:I}; 
 "select Col1,Col3,Col2*Col4 
  where Col3 is not null"); 
 "select Col1,sum(Col3) 
  group by Col1 
  pivot Col2"); 
 "select Col1,Col2,Col3,Col5,Col4"; 1); {2\3\4\5}; 0)))

    0