To sum multiple columns conditionally, using one criteria, you can use a formula based on the SUMPRODUCT function. In the example show, the formula in H5 is:

=SUMPRODUCT((B5:B10="red")*(C5:E10))

How this formula works

This first expression in SUMPRODUCT is the criteria, checking if cells in B5:B10 contain “red”. The result is an array of TRUE FALSE values like this:

{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}

This is multiplied by the values in range C5:E10:

{1,6,2;8,6,8;5,2,6;2,6,7;7,4,5;5,3,1}

The result inside SUMPRODUCT is:

=SUMPRODUCT({1,6,2;0,0,0;0,0,0;2,6,7;0,0,0;0,0,0})

which returns 24, the sum of all values in C5:E10 where B5:B10=”red”.

Contains type search

SUMPRODUCT doesn’t support wildcards, so if you want to do a “cell contains specific text” type search, you’ll need to use criteria that will return TRUE for partial matches. One option is to use the ISNUMBER and SEARCH functions like this:

=SUMPRODUCT((ISNUMBER(SEARCH("red",B5:B10)))*(C5:E10))