Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

i would like to have a formula that will perform a sumproduct if the header has like tom

IF(($O$4:$V$4 "<>*tom*"),

then perform the sumproduct function

SUMPRODUCT(--SUBSTITUTE(TEXT($O$8:$V$8,"0.00"),".",":")))

then a similar function IF(($O$4:$V$4 "*tom*"),

then perform the sumproduct function

SUMPRODUCT(--SUBSTITUTE(TEXT($O$8:$V$8,"0.00"),".",":")))

thanks

IF(($O$4:$V$4 "<>*tom*"),

then perform the sumproduct function

SUMPRODUCT(--SUBSTITUTE(TE

then a similar function IF(($O$4:$V$4 "*tom*"),

then perform the sumproduct function

SUMPRODUCT(--SUBSTITUTE(TE

thanks

but it will have to substitute the "." with ":" in the range $O$8:$V$8

which it the SUBSTITUTE(TEXT($O$8:$V$8,

SUMPRODUCT(--SUBSTITUTE(TE

or

if $O$4:$V$4 like tom

Kevin

If row 4 contains "tom":

=SUMPRODUCT((NOT(ISERROR(S

If row 4 does not contain "tom":

=SUMPRODUCT((ISERROR(SEARC

Kevin

Provide a formula to sum the values in O8:V8 if the value in the same column in row 4 contains "tom", and a second formula to do the same if the value in row for does not contain "tom". The values are times but with periods versus colons.

Kevin

The fix for Kevin's formula with the blank cell problem is to add 0 to O8:V8

=SUMPRODUCT((NOT(ISERROR(S

Note that if O8:V8 contain more than two digits after the decimal point, this formula will give a different answer than the one using TEXT.

for the header that does not includes tom nothing return the accurate number. thanks

=SUMPRODUCT(ISERR(SEARCH("

Could you please post a workbook showing some sample data and the expected result?

Depending on what your data looks like, there could be a simpler formula. I am particularly interested in knowing how many digits you have to the left and right of the decimal point, and whether there might be blank cells.

Brad

All Courses

From novice to tech pro — start learning today.

=SUMIF(O4:V4,"*tom*",O8:V8

If there might be 3 or more digits after the decimal point--and you want to round two digits--then consider:

=SUMPRODUCT(ISNUMBER(SEARC

Note that Kevin's suggestion will return an error value if one of the cells in O8:V8 is blank.