sumproduct if

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
SvgmassiveAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
Please describe the function in English without the formulas.

Kevin
0
 
SvgmassiveAuthor Commented:
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
Get expert help—faster!

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

 
SvgmassiveAuthor Commented:
the substitute convert the entries to a time format
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
SvgmassiveAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
byundtCommented:
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
 
SvgmassiveAuthor Commented:
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
 
byundtConnect With a Mentor Commented:
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
 
byundtCommented:
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.

Brad
0
 
SvgmassiveAuthor Commented:
=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
 
SvgmassiveAuthor Commented:
the solutions that i got were remarkable....thanks to all  that contributed
0
All Courses

From novice to tech pro — start learning today.