Google Sheets sum of columns where cell value matches multiple columns in another sheet

Question

I need a formula in Google Sheets which will lookup a cell across multiple columns in another sheet and return the sum of cells where that value is contained.

Here's my sheet:

https://docs.google.com/spreadsheets/d/1Z2XUqG1_3g0N__5Ab1R-8Dr8Bk-Z88x6nYvOdhYw78Q/edit?usp=sharing

Answer 1

just filter it inside of SUM like:

=SUM(FILTER(Sheet2!C$2:C, (Sheet2!A$2:A=A2)+(Sheet2!B$2:B=A2)))

Answer 2

You could try sumproduct:

=sumproduct(Sheet2!A:A=A2,Sheet2!C:C)+sumproduct(Sheet2!B:B=A2,Sheet2!C:C)-sumproduct(Sheet2!A:A=A2,Sheet2!B:B=A2,Sheet2!C:C)

The first two sumproducts add col C values if either col A or col B matches the colour.

The third sumproduct removed the double count where both col A and col B match the colour.

Answer 3

You can Add 3 columns red flag,green flag and blue flag.

then use IF function and but 1 if it belongs to the color.

=if( OR( A2="red",B2 ="red"),1,0)

then you create a new 3 columns red count, green count and blue count and multiply Quantity by the color flag.

=C2*H2

then use Sum function to add it in the other sheet

=sum(Sheet2!K2:Sheet2!K10)