x
Solved

# sumproduct if

Posted on 2013-06-29
Medium Priority
641 Views
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
0
Question by:Svgmassive
• 6
• 4
• 4

LVL 81

Expert Comment

ID: 39287287
Please describe the function in English without the formulas.

Kevin
0

Author Comment

ID: 39287293
sum the range \$O\$8:\$V\$8 if  \$O\$4:\$V\$4  not like tom

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

which it the  SUBSTITUTE(TEXT(\$O\$8:\$V\$8,"0.00"),".",":") part of the sumproduct
SUMPRODUCT(--SUBSTITUTE(TEXT(\$O\$8:\$V\$8,"0.00"),".",":")))

or
if  \$O\$4:\$V\$4   like tom
0

Author Comment

ID: 39287297
the substitute convert the entries to a time format
0

LVL 81

Expert Comment

ID: 39287302
You have not distinguished a difference between cells that are like tom and cells that are not. In each case you are summing cells \$O\$8:\$V\$8.

Kevin
0

Author Comment

ID: 39287312
it  varies so we look in range \$O\$4:\$V\$4   to find any cell that has a value like "tom" ,then sum the matching  cells \$O\$8:\$V\$8  after converting the values \$O\$8:\$V\$8  to timevalues
0

LVL 81

Expert Comment

ID: 39287313
Perhaps this.

If row 4 contains "tom":

=SUMPRODUCT((NOT(ISERROR(SEARCH("*tom*",O4:V4))))*SUBSTITUTE(O8:V8,".",":"))

If row 4 does not contain "tom":

=SUMPRODUCT((ISERROR(SEARCH("*tom*",O4:V4)))*SUBSTITUTE(O8:V8,".",":"))

Kevin
0

LVL 81

Expert Comment

ID: 39287318
What I provided above is a solution for this request:

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
0

LVL 81

Accepted Solution

byundt earned 2000 total points
ID: 39287359
Do the numbers in O8:V8 include only two digits after the decimal point? If so, you could use:
=SUMIF(O4:V4,"*tom*",O8:V8)/14.4

If there might be 3 or more digits after the decimal point--and you want to round two digits--then consider:
=SUMPRODUCT(ISNUMBER(SEARCH("tom",O4:V4))*SUBSTITUTE(TEXT(O8:V8,"0.00"),".",":"))

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

LVL 81

Expert Comment

ID: 39287368
If O8:V8 contain only numbers like 0.23, then my two suggestions will give the same answer. If there is a number like 1.23, then I suspect the SUMIF will give what you might consider a wrong answer.

The fix for Kevin's formula with the blank cell problem is to add 0 to O8:V8
=SUMPRODUCT((NOT(ISERROR(SEARCH("tom",O4:V4))))*SUBSTITUTE(O8:V8+0,".",":"))
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.
0

Author Comment

ID: 39288189
byundt your formula =SUMPRODUCT(ISNUMBER(SEARCH("tom",O4:V4))*SUBSTITUTE(TEXT(O8:V8,"0.00"),".",":")) is the right on point for the header that has tom

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

LVL 81

Assisted Solution

byundt earned 2000 total points
ID: 39288192
To add up the corresponding cells when O4:V4 does not include "tom", you might use:
=SUMPRODUCT(ISERR(SEARCH("tom",O4:V4))*SUBSTITUTE(TEXT(O8:V8,"0.00"),".",":"))
0

LVL 81

Expert Comment

ID: 39288197
Svgmassive,
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.

0

Author Comment

ID: 39288206
=SUMPRODUCT(ISNUMBER(SEARCH("tom",O4:V4))*SUBSTITUTE(TEXT(O8:V8,"0.00"),".",":"))

=SUMPRODUCT(ISERR(SEARCH("tom",O4:V4))*SUBSTITUTE(TEXT(O8:V8,"0.00"),".",":"))

These formulas did the trick the last one thanks a  million byundt
0

Author Closing Comment

ID: 39288208
the solutions that i got were remarkable....thanks to all  that contributed
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.