Search code examples
google-sheetssumifs

Google Sheets: Repeat SUMIF over multiple columns with different sum ranges and criteria

Here is my example sheet: https://docs.google.com/spreadsheets/d/1wRvpRufL4JlY8AprwHCZvvXlVijg2u2Y2jG-6b62FEA/edit?usp=sharing

enter image description here

This is a simple stock register sheet, where I am keeping stock in "Stock Sheet". I am trying to add all the out movement in "Out" column. So It should basic search for particular product like "Prod 1" and look for it in "Movement" sheet and "Prod" sheet and sum all the movement. However in "Prod" sheet there are multiple columns where It can be present. So I need to check columns "Raw 1", "Raw 2" and add quantity in each.

In simple terms, in Prod sheet, products being used will sum in "Out" and produced in "In" column.

Thank you in advance. I can do this by adding SUMIF multiple times for each column, I am trying to find more efficient way to do it as this sheet will expand a lot and single formula will then go in multiple lines.

Solution

  • You can collect all data in a new tab (Prod_plus) by

    =query({Prod!F2:G;Prod!H2:I;Prod!J2:K;Prod!L2:M;Prod!N2:O},"select Col1,sum(Col2) group by Col1")

    enter image description here

    and then

    =SUMIF(Movement!$B$2:$B,A2,Movement!$G$2:$G)+SUMIF(Prod_plus!$A$2:$A,A2,Prod_plus!$B$2:$B)