Quick, clean, and to the point

Sum if one criteria multiple columns

Excel formula: Sum if one criteria multiple columns
Generic formula 

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:


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:


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


The result inside SUMPRODUCT is:


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:


See this page for more information about how this works.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.